SOLVED: Prefilter Help

[daduts]

Greetings,

Thanks for the help on yesterdays prefilter problem. I'm afraid I have a 2nd one for you.

Here's what my list now looks like....



I have events, account owners(parents) and players(children).

This is the registration list for an event.

In this case....the account owner has 3 players.

2 are registered for the event. The params field includes those 2.

I need to filter this list to filter out the one player who is not registered for the event.

The regselect field includes a unique id for each player.

I've been trying to create a prefilter along the lines of....

params CONTAINS regselect

I've tried various formats based on your Wiki and Forum posts but have not hit a winner yet.

FYI, the raw data in the params field looks like:

{"field1":"Junior(1)","field2":"Max (2)","field3":"0"}

Thanks for your ideas and attention,

Dave

 

[cheesegrits]

It looks like those params are just storing the "first name", not a unique "regselect"?

-- hugh

 

[cheesegrits]

Unless that's the number in parens?

-- hugh

 

[cheesegrits]

ooops, duh, I didn't see the regselect column in the image.

Yes, params contains regselect should do it.

Try enabling Fabrik debug (in the global options), and append &fabrikdebug=1 to your URL (or ?fabrikdebug=1 if it's the first query string arg). Then look at the getData query in the debug output. See what the actual WHERE clause being generated is.

-- hugh

 

[daduts]

Thanks hugh,

I'm still struggling with this one.

Here's what I got....

I've modified my table a little so we are now looking for rows WHERE params CONTAINS regmatch .

I've been playing around with the prefilters and debug mode but have not been able to get the prefilter to produce the query I think I want.

ie... with this pre-filter...



I get this Where statement.....

WHERE ( h39rp_jev_attendees.at_id = '57' AND h39rp_jev_attendees.params LIKE '%\'players___regmatch\'%' )


What I think I want is....

WHERE ( h39rp_jev_attendees.at_id = '57' AND h39rp_jev_attendees.params LIKE 'players___regmatch' )

I've tried the various Type's and do not get the desired results.

Still not sure if I've got the wrong query or the wrong prefilter format.

Your thoughts ?

Thanks,

Dave

 

[cheesegrits]

Try

condition: contains
value: CONCAT('%(', players___regmatch, ')%')
type "no quotes"

-- hugh

 

[daduts]

ehhhhh..... not so good

WHERE ( h39rp_jev_attendees.at_id = '57' AND h39rp_jev_attendees.params LIKE %CONCAT(\'%(\', players___regmatch, \')%\')% )

"Error while rendering position" where the table is supposed to be.

Thank you sir, may I have another ?

: D

 

[cheesegrits]

Meh, forgot we'd automagically add those %'s.

I may have to tweak our code so as not to add those, and not to quote the quotes, if you use "no quotes" with a CONTAINS, on the assumption that "I know what I'm doing".

-- hugh

 

[cheesegrits]

Try this:

https://github.com/Fabrik/fabrik/commit/d350b31b4c860132bd82af1170d01ed1c646d201

If you've updated recently, you can just grab the one file from ...

https://raw.githubusercontent.com/F...d201/components/com_fabrik/models/element.php

... and upload it to the appropriate location on your server. Might wanna save a copy of the original in case I moofed the code, as I haven't tested it.

What this should do is not add the %'s around your CONCAT, and not escape those quotes.

-- hugh

 

[cheesegrits]

PS ... and yes, I know FABRKFILTER_NOQUOTES has a typo ... but that's how it's spelled where we define that constant ... next commit I'll fix all occurrences of it in the code.

-- hugh

 

[daduts]

The new noquotes code did the trick and the winning filter ended up being....

condition: contains
value: CONCAT( '%', `players`.`regmatch` , '%' )
type: noquotes

Many thanks,

Cheers,

: D

 

[cheesegrits]

Yay!

-- hugh