Filtrer by translatable field

Created 7 years ago by sdr1989

Hello everyone I got this code:

{% set news = entries('news').where('status', '=', 1).orderBy('publish', 'DESC').paginate(10) %}

wich works fine, but if i try to filter by a translatable field, like the field name for example, i get a mysql error:

{% set news = entries('news').where('status', '=', 1).where('name', 'like', '%john%').orderBy('publish', 'DESC').paginate(10) %}

the error i get: Column not found: 1054 Unknown column 'name' in 'where clause' (SQL: select count(*) as aggregate from meis_news_news where status = 1 and name like %john%)

I get the same errore even I use the get() instead of paginate()

How can i make the second query work? Thanks

sdr1989 — 7 years ago

Please, anyone?

Link

piterden — 7 years ago

{% set builder = entries('news') %}
{% set table = builder.connection.table %}

{% set news = builder
    .where('status', 1)
    .leftJoin(
      table ~ '_translations AS T',
      table ~ '.id',
      '=',
      'T.entry_id'
    )
    .where('T.name', 'like', '%john%')
    .orderBy('publish', 'DESC')
    .paginate(10)
%}

Link

sdr1989 — 7 years ago

Thanks for your reaply @piterden

It kinda works but: {% set table = builder.connection.table %} is empty in my case, if I put myself the name of the connection table than it works, the only problem left is that it gets every localitations (i.e. en - sp - de) of the model and not the one I'm actually using ( i.e. en) .

Link

piterden — 7 years ago

{{ model.getTable() }} should returns the table name.

Link