Advanced datajoin element and filter problem

[piri314]

Hi,

I have a table/form for EVS (european voluntary service) volunteers. The Volunteer have to choose one or more hosting organisations thanks to a group with a database join element wich looks for pre-filtered values (available organisations) and shows Concat text.

My problem is that if I select the option "make a filter" on this element the result is :

Warning: Invalid argument supplied for foreach() in /homez.43/euromedih/www/components/com_fabrik/fabrik_table.class.php on line 2493

Warning: sort() expects parameter 1 to be array, null given in /homez.43/euromedih/www/components/com_fabrik/fabrik_table.class.php on line 2506

Warning: Invalid argument supplied for foreach() in /homez.43/euromedih/www/components/com_fabrik/fabrik_table.class.php on line 2520

Warning: array_keys() [function.array-keys]: The first argument should be an array in /homez.43/euromedih/www/components/com_joomfish/includes/joomfish.class.php on line 423

Warning: Invalid argument supplied for foreach() in /homez.43/euromedih/www/components/com_joomfish/includes/joomfish.class.php on line 423

Warning: Invalid argument supplied for foreach() in /homez.43/euromedih/www/components/com_fabrik/fabrik_table.class.php on line 2528
Unknown table \'jos_sve\' in where clause SQL=SELECT DISTINCT(id_annu) AS elVal, CONCAT(HEI,\'-\',structure,\' (\',ville,\')\') AS elText \n FROM jos_manifs\n INNER JOIN jos_annuaire ON jos_manifs.id_annu = jos_annuaire.id_annu_annu \n WHERE jos_sve.id_manifs > 134 AND TRIM(id_annu) <> \'\' GROUP BY elText ASC

Any idea ?

Thanks
PIRINA

 

[cheesegrits]

Can you post a screen shot of how your join element is set up?

-- hugh

 

[piri314]

The screen shot

Thanks
PIRINA

 

[cheesegrits]

OK, the problem seems to be you are referencing the jos_sve table in your WHERE clause, but it's not being included with a JOIN anywhere.

You'll have to add a JOIN to the element, and join the jos_sve table on whichever column is appropriate.

-- hugh

 

[piri314]

Sorry Hugh, can not understand how and where to make a join.

Thanks
Pirina

 

[cheesegrits]

Within the join element you currently have.

In your 'where' clause you are referencing the jos_sve table, but you aren't joining that table, so the query can't work.

You'll have to "Ajouter une jointure", and left join the jos_sve table (in the same way you are joining the jos_annuaire table) on whatever column is appropriate (I can't tell you that, because I don't know how your tables relate to each other).

-- hugh

 

[piri314]

Sorry, tried more times
---------------------------------------
1.db error for SELECT id_annu AS value, CONCAT(HEI,'-',structure,' (',ville,')') AS text FROM jos_manifs INNER JOIN jos_annuaire ON jos_manifs.id_annu = jos_annuaire.id_annu_annu INNER JOIN jos_annuaire ON jos_sve.id_manifs = jos_annuaire.id_annu_annu WHERE id_annu IN ( '2058', '1596', '2059', '2068', '', '2072', '2055', '2056', '2076', '2074', '2057', '376', '2075', '2073', '2077', '2070', '1647' )

Help, please

PIRINA

 

[cheesegrits]

Can you PM me backend login, remember to include a link a back to this thread in your PM.

-- hugh

 

[piri314]

I posted you my Admin details

Thanks
PIRINA

 

[cheesegrits]

OK, I should get to it later today ... you are #3 in my "laying on of hands" queue.

-- hugh

 

[cheesegrits]

Piri - I'm logged in to your site, but I don't seem to be able to get it to error out as you described above.

I should be around for most of the weekend. Can you Skype/MSN me next time you are online? I'm usually around from about 10am till midnight, CST (so I'm about 6 hours behind you).

-- hugh

 

[piri314]

Hi Hugh,

my Skype is the same username like Fabrik. I added you in my contacts.

Wainting for a contact,
Kinldy
PIRINA

 

[cheesegrits]

I have you on my Skype list, you are offline right now. Ping me whenever you are next online. Note that if I don't reply, it doesn't mean I'm ignoring you, it just means I'm not at the computer. I tend to keep all my IM's logged in all the time.

-- hugh

 

[piri314]

I just realized that it's maybe a problem Service Pack 2, because we have a PC with SP 1 an the filter works !!!
Can you check this ?

Thanks
PIRINA

 

[cheesegrits]

Just to summarize our Skype session just now.

The problem turned out to be an issue with table pre-filters and join element filters combined. Basically, we append any table pre-filter conditions to filter queries, so your element filter dropdown doesn't show records which are filtered out of the main table by a pre-filter.

The problem was, because your Choix join element didn't reference the main table at all, appending the table pre-filter WHERE to the element filter query was blowing up, 'cos the FROM part didn't include the main table.

I've uploaded a fixed file, which should only include the main table pre-filter conditions on the filter if the element filter actually references the main table.

I haven't comitted the fix to SVN yet, I'd like you to sanity check your site and make sure this fix doesn't introduce any other bugs!

Let me know how it goes.

-- hugh