Weighted search

[Beltshassar]

I am trying to create a custom search filter, like the existing SearchFilter.php, but with the result ordered by a calculated "weight"

I want to lookup multiple search-words in several table fields with different priorities, adding up to a total value: "weight".
The result should be ordered by the value of weight.

So far I am able to build a working sub-query, but the ordering is lost, when I return the ConstraintInterface as a "->in"-query.
"setOrderings" is available, but is not really useful, since weight only exists in my sub-query.

I'm starting to suspect that "filters" is not really fit for my use case.
Have I missed something, or should I try another way, perhaps using "custom endpoints"?
I'd be grateful for any advice.

[klamparski]

Hi @Beltshassar. Indeed it will be hard or even impossible to achieve with pure Extbase's QueryBuilder because it does not give a lot of features. For sure it can't handle subqueries or ordering by dynamic properties (can't test it now but I think an error will be thrown because it's based on model properties and that will be unknown property).

Did you tried to use MySQL's view? Such view can be a wrapper for more complex SELECT query. If you would like to use this approach it will be required to create separate model and TCA definition for such view to make Extbase working as expected. Generally it would be entity like all the others but based on MySQL view instead of real table.