Search through a smart field that was calculated from a different table

Help me!
1

hello

i have a smart field that was calculated from a different table and i wanted to have search through that field. and the only option (at least what shown in the documentation) for me is to add my query conditions to the where_clause of the query, but in my case i need to add a table to the from_clause as well which i’m not sure how to work around.
my database structure is as follows:

  • users table
  • user_translations table: which contains the user names in translated format to support multilingual users.

the calculated value is the user name which is brought from the user_translations table and id like to have search through these names. i’m using the rails Globalize gem to translate the names if that helps.

2

Hi,

For smart fields, from doc: “The collection name must be the same as the model name”.
I am afraid you can’t use a smart field to cover this need.

Can you please give more information about what you currently have:
-What are the tables structure
-How looks the request you would like to achieve
-… any technical info allowing us to find a out-of-the solution for your request

Regards

3

i have s users table that contains all the user info except for the name. the name is stored in another table called user_translations since the user has the option to enter his name in two languages Arabic and English.
each name in the user_translations table is mapped to a user in the users table with the key user_id.
now in the admin dashboard when i display the user info it doesn’t show the user’s name since its stored in a different table(user_translations) but i wanted to be able to view the name in the users table so i could see all user info in one view. so what i did is create a smart field that maps the users table with the user_translations table and it fetches the name from the users_translations table and puts it in the smart field and that works fine.

now as an extra step i wanted to be able to search through these names from the same view (users table view). i saw the documentation and its possible to make a smart field searchable but only by adding my conditions to the where_clause of the query but in my case i need to manipulate the from_clause as well since my data is being brought from a different table.
i would appreciate it if you could help me with this. if there’s a smarter way to do this please let me know.

sorry for the long message and thank you.

4

Hi @huss,

I am asking for my teammates about the capacity of the query object explained in the doc.

5

Please have a look at this snippet

You can add includes (joins) to the query.
Then you can add the filter condition you want.

Regards

6

this looks very promising but do you have the rails version for that code? (my integration to your dashboard is through my rails app).

7

Hello @huss,

Does the snippet help you implement what you need?
Do you experiment any issues by manipulating the from_clause keyword?

Let me know :slight_smile:

8

well the snippet only proves that what i want to achieve is possible but it doesn’t show me how to do it no.
the language i’m using is ruby on rails so its probably done differently. the snippet you sent was in javascript which isn’t very helpful for me.
i would like to know how to achieve the same result but in ruby. it would also be helpful if you could show me a snippet that shows how to manipulate the from_clause in ruby.

thank you

9

I think in your case you could something like:

search_name = lambda do |query, search|

    # Injects your new filter into the WHERE clause.
    query.joins(:user_translations)
    query.where_clause.send(:predicates)[0] << " OR user_translations.name = '#{search}'"

    query
  end

Please log the sql query, it will help you implementing what you are trying to achieve.

10

i tried doing that but i get this error: ( Forest :deciduous_tree::deciduous_tree::deciduous_tree: Records Index Count error: PG::UndefinedTable: ERROR: missing FROM-clause entry for table “user_translations” )

and i checked the query but the join doesn’t appear in it so i don’t think its actually taking effect.

11

Hello @huss,

Could you log the generated SQL query here? And share more details on the code you actually implemented?

It would be easier for us in order to help you debug the issue.

Thanks

12

finally after a lot of trial and error it works.
here’s the code:

search_translated_names = lambda do |query, search|
    # Injects your new filter into the WHERE clause.
    query = query.joins("LEFT OUTER JOIN user_translations ON user_translations.user_id = users.id")
    query.where_clause.send(:predicates)[0] << " OR LOWER(user_translations.name) LIKE '%#{search.downcase}%'"
    query
  end

this code joins two tables on a specific field which allows you to search through a smart field that was calculated from a different table but is in relation with the current table/collection

3 Likes