Using fields from related table in filter

[intermike]

Greetings.

Is there a possibility to make search by certain fields in table and related table to find any row that contains search word in any of fields?

Cortex contains "has" function that allows to narrow query, but this doesn't fit my needs. It gives me something like: name like ? AND product.title like ?.

I need something like: name like ? OR product.title like ?, where is product is belongs-to-one relation.

I see the only way to load IDs of products that filtered by title like ?. And then use name like ? OR product IN ?. This is probably good for JIG, but not good for SQL.

I would appreciate for any suggestions. Thanks.

Mike.

P.S. It would be awesome to have possibility to use related table fields directly in filter.

[intermike]

An addition.

How can I sort by related table field? Like: order by product.title

Thanks.

[ikkez]

hi mike.
sorry but I did not had the time to have a deeper look at this yet. But some things that came to my mind so far:
Yes the has() method currently just appends the query condition with a logical AND. What you want is an OR at this point, alright. The tricky part is how to define this, and moreover how to mix them the way we want the conditions. The most obvious way is to use the related fields in the filter directly,.. indeed that's a great idea. That way it would be easy to define the query condition with all related "has" filters directly in one filter. It would solve the issue of defining which related fields are strict and which are optional like (name like ? and category.title like ? ) OR product.title like ?. Basically this looks great, but unfortunately it becomes problematic, since it suggests that things like this should work too: field1 like ? AND ((relationA.field1 > relationB.field2) OR (field2 <= relationC.relationD.field1))
Of cause it would be great if that works, but comparisons especially along multiple different relations is already hard to unify for all the sql engines,.. not to mention how the jig or mongo derivatives could work. I really like the idea though, I guess it'll take 2 months of nightly headaches to build something usable this way. So maybe something easier could do the job as well for now.
Maybe just a custom query using the DB directly? you could add the sorting by a related table field there as well. Another option is to sort the results afterwards, or use the product model to load and sort the results, and get the records of your interest through their relation.
I wish i could offer you some more straightforward solutions, but I need to think about that first. Ideas are welcome ;)

[intermike]

Hi Christian.

Thanks for your reply. I've already solved my issue in current project that focused at using flat-file database.

Unfortunately I am not familiar with all SQL engines that Cortex supports. But I think they all should support joins and ordering by reference fields. So creating SQL query based on even complex conditions like relationA.field1 > relationB.field2 would not be a big problem. So the main task is making JIG and Mongo mappers to support such filters and order terms. Probably there would no talks about performance - that engines for small amount of data and simplest joins.

I guess all conditions atoms that contains reference fields should be replaced with references ids checks like category_id IN (...) before main query run. List of ids can be gotten by comparing column(s) according condition even using deep level references. So something like indexes on-fly. May be even caching indexes with used reference fields.

I am not sure about ordering, but it can be done by adding temporary column to main table based on used reference fields like your virtual fields.

I am sorry... I am not so familiar with F3 and Cortex - just several days - So I can't be wise adviser.

P.S. Thanks for you work. This framework is quite reliable for small stand-alone apps.

P.P.S. Btw I made interface of F3+Cortex and https://www.datatables.net in server-side mode - Works cool. The only thing is ordering and filtering by reference fields. That is why I issued current subject. May be when we solve this subject task I will wrap interface in plug-in for F3 and post it on Git.

[ikkez]

Hi Mike. I thought about that some time. I think it would be possible to add your feature request. With some query parsing and magic functions that hooks some joins and/or reference checks with IN (...) it could work, sure. Cortex is already doing this sometimes in that way. Nevertheless, the chances to be made for this would be quite big and i'm pretty sure it reveals tons of edge cases that needs to be considered. So I can try to refactor the query builder to add those features for Cortex 2.0. To make it all work with Jig and Mongo, I guess those framework mappers needs to be updated too. At least we need grouping support for Jig. I started working on that some time ago bcosca/fatfree#616 but maybe needs some refining first. Unfortunately I got other things to do atm, so I don't expect this issue to be solved this year. Your F3+Cortex interface sounds interesting. I also had something like that in my mind, but not the time to build it.

[intermike]

Thanks for considering this request :)
Good luck with Cortex 2.0.
We are looking forward.