Databasejoin filter

[mogy74]

Hi to all,
i need to make a filter result in a database join , i follow the wiki but looks like it don't work, because i see on my list all aviable data without filter,

this is the sintax i use:

WHERE {thistable}.`nome`
IN ( SELECT elenco_istruttori.nome FROM elenco_istruttori,elenco_istruttori_5_repeat
WHERE elenco_istruttori.id = elenco_istruttori_5_repeat.parent_id
AND elenco_istruttori_5_repeat.competenze = 2)

i would have in list only the "nome" where competenze is 2
thanks in advance to everyone

 

[Bauer]

I don't think you provided enough information.
What is the name of the table that the databasejoin element is in?
And what is the name of the table that the datbasejoin element is using (joined to)?
Is this databasejoin element configured as a simple single dropdown (or radiobutton) element or is it configured as a multiselect - such as a multiselect dropdown or checkboxes ?

You should test your embedded SELECT query independently using phpMyAdmin.
This isn't going to work until that SELECT query does - and if you're trying to query a repeat table that SELECT query isn't using the correct syntax.

All that said, if everything is as I think it is (and this dbj element is a multiselect dbj and you're making more of it than you need) - you might want to try simply...
WHERE elenco_istruttori_5_repeat.competenze = 2

FYI... The easiest way to debug dbjoin elements is to look at the syntax that Fabrik has created from your configuration - and test that by cutting and pasting the resulting query (using fabrikdebug) into the phpMyAdmin SQL box and running and tweaking that query until you get it right.
Then make the adjustments in your WHERE clause of the configuration.
To do that just add &fabrikdebug=1 to the end of the browser url, refresh the page, and locate the line...
[dbj_element-name] databasejoin element: get options query.

 

[mogy74]

Thanks for your reply and your suggest
the name of the table of databasejoin is "elenco_istruttori" where is stored data of instruttori , they can be have one or more "competenze" and are stored in realted table "elenco_istruttori_5_repeat".
so i need do filter about it i try whit number "2" as a simple test this number is stored in columm "competenze" .

now i try as you said to do it on phpmyadmin . and post back a working query
Thanks

 

[mogy74]

select * from elenco_istruttori,elenco_istruttori_5_repeat where elenco_istruttori.id = elenco_istruttori_5_repeat.parent_id AND elenco_istruttori_5_repeat.competenze =2

This querry on phpmyadm workin

Thanks for help

 

[mogy74]

ok i did it
this is the working filter
WHERE {thistable}.id IN (SELECT parent_id FROM elenco_istruttori_5_repeat WHERE competenze = {compila_planning_14_repeat___corso_raw})

competenza now is set on ajax control from the raw data,

how if i want add a new condition?
i try this syntax but i have sql error
WHERE {thistable}.id IN (SELECT parent_id FROM elenco_istruttori_5_repeat WHERE competenze = {compila_planning_14_repeat___corso_raw}) AND IN (SELECT parent_id FROM elenco_istruttori_7_repeat WHERE disponibile = {compila_planning___sede_raw})

how to add an "AND" at the query?

thanks for help

 

[Bauer]

You have to be careful when using fabrik placeholders in MySQL queries. Be sure to surround placeholders in elements stored as strings with single quotes.

e.g. if competenze is stored as a string it should be
WHERE competenze = '{compila_planning_14_repeat___corso_raw}'

And if numeric (no single quote used) you also have to be careful -
If that placeholder could possibly be blank, it would throw an error - so in that case you should always use an OR condition in the placeholder..
WHERE competenze = {compila_planning_14_repeat___corso_raw||0}

Also you forgot to include {thistable}.id in the AND condition (between AND and IN ).

So after considering the issues mentioned above that can arise with strings and numbers, try...

WHERE {thistable}.id IN (SELECT parent_id FROM elenco_istruttori_5_repeat WHERE competenze = '{compila_planning_14_repeat___corso_raw}') AND {thistable}.id IN (SELECT parent_id FROM elenco_istruttori_7_repeat WHERE disponibile = {compila_planning___sede_raw})

I'm not sure how the Fabrik code will parse that - but if there is still an issue you can try separating the 2 WHERE conditions with parenthesis - like...

WHERE ({thistable}.id IN (SELECT parent_id FROM elenco_istruttori_5_repeat WHERE competenze = '{compila_planning_14_repeat___corso_raw}')) AND ({thistable}.id IN (SELECT parent_id FROM elenco_istruttori_7_repeat WHERE disponibile = {compila_planning___sede_raw}))

 

[mogy74]

yes all the table are done by fabrik is a little bit confused the work i try to do.. elenco_istruttori is the main table compila_planning is the table of the module where i try to filter the result of istruttori it work well i'm just cofused about the syntax to use with fabrik i have to look into two (and maybe more in future) different table and filter by the raw result of the form without AND work well but how is the correct syntax for use also AND can you make a working exemple?

Thanks a lot

 

[Bauer]

yes all the table are done by fabrik is a little bit confused the work i try to do.. elenco_istruttori is the main table compila_planning is the table of the module where i try to filter the result of istruttori it work well i'm just cofused about the syntax to use with fabrik i have to look into two (and maybe more in future) different table and filter by the raw result of the form without AND work well but how is the correct syntax for use also AND can you make a working exemple?

Thanks a lot

I realize we just crossed replies, sorry.
After re-reading, I better understood your last reply so I removed my rant and replied to your actual question.
Glad I could help.

 

[mogy74]

thanks a lot! now working
i can suppose that i need another AND i just append it like the exemple? i mean can i have more then one AND in the same query?

thanks

 

[Bauer]

thanks a lot! now working
i can suppose that i need another AND i just append it like the exemple? i mean can i have more then one AND in the same query?

thanks

In theory, don't see why not.
Though keep in mind you are running (each of) the select queries on each row in the databasejoin select. So on a databasejoin using a large table and/or a lot of conditional AND conditions assigned to the WHERE, you'll probably run into some timing issues.

 

[cheesegrits]

I'm not sure how the Fabrik code will parse that

We don't actually parse the WHERE clause, we just append it as-is to the basic query for the join ("SELECT id, whatever FROM sometable"), so it's subject to all the usual MySQL operator precedence rules.

Performance wise, if these joined tables are large, it's worth checking to see if you have indexes on the fields you use in those subqueries. Fabrik automatically creates indexes on the obvious ones (like parent_id), but others will only have indexes if you have designated them as filters. So for example the subquery ...

(SELECT parent_id FROM elenco_istruttori_5_repeat WHERE competenze = '{compila_planning_14_repeat___corso_raw}')

Have a look at the elenco_istruttori_5_repeat table structure with phpMyAdmin and make sure that there is an index for 'competenze'. If not, you can create one by hand. Doesn't matter what you call it. Having an index will massively improve the speed of those queries for large tables.

Basically, Fabrik is very good at making sure anything you do with Fabrik itself creates the right indexes to help performance. So we index anything you specify as a "group by", or you put a pre-filter on, or set as an element filter, etc. But when you write your own "free form" queries, we really have no way of telling (or at least it would be a lot of tedious work) to figure out if the queries you've written would benefit from indexes on certain fields.

-- hugh