83 Posts
squareweb
5 years ago
Topic

Hi, I'm having a hard time with the search join plugin.

I've tried to swap fields, read this post too: https://www.seblod.com/community/forums/fields-plug-ins/search-join-unknown-column-t0-child-id-in-on-clause

It works fine only if the search-fields are filled. If all search-fields are emptied or activating "reset" and filter then an error occurs:

Unknown column 't0.lsm_lrm_id' in 'on clause'

Any ideas are welcome.

Get a Book for SEBLOD
4229 Posts
Kadministrator
5 years ago
2
Level 1

Hi,

activate debug to see the query produced with/without each search field. I presume that without the filter some table needed for join is missing, the solution would be to add this table in the search join before your join so it is always included and/or base the join on some table that is always in the query e.g. cck_core table. You can join the same table twice and combine it with other joins (inner, right etc) to get the necessary effect. 

83 Posts
squareweb
5 years ago
0
Level 2

Hi Klas,

Thanks for this answer, the thing is that when I activate debug Joomla is not showing the debug-area below the screen in case of an error...

I've switched templates but this doesn't help, is there another way to catch the debug-info?

BTW, examining the error message (Unknown column 't0.lsm_lrm_id' in 'on clause') it is telling me that the system is looking for a field that indeed doesn't exist in t0 (cck_core_table).

Below is the full query when fields are filled and the query executes fine, I cannot replicate the error-query (yet):

SELECT t0.id AS pid,t0.pk AS pk,t0.pkb AS pkb,t0.parent_id AS parent,t0.author_id AS author,t0.author_session AS author_session,t0.cck AS cck,t0.storage_location AS loc,tt.id AS type_id,tt.alias AS type_alias
 FROM `nu1pc_cck_core` AS t0
 LEFT JOIN `nu1pc_cck_store_form_lesmateriaal` AS t2  ON t2.id = t0.pk
 LEFT JOIN `nu1pc_content` AS t3  ON t3.id = t0.pk
 LEFT JOIN `nu1pc_cck_store_form_leermiddel` AS t1  ON t1.id = t2.lsm_lrm_id
 LEFT JOIN `nu1pc_cck_core_types` AS tt  ON tt.name = t0.cck
 WHERE t3.state = 1  AND t3.access IN (1,1,2,3,6,8)  AND ( t3.publish_up = '0000-00-00 00:00:00' OR t3.publish_up <= '2018-10-19 13:16' )  AND ( t3.publish_down = '0000-00-00 00:00:00' OR t3.publish_down >= '2018-10-19 13:16' )  AND t0.cck = 'lesmateriaal'  AND t2.lsm_dt_id LIKE '%458%'
 ORDER BY t3.title ASC  LIMIT 25
83 Posts
squareweb
5 years ago
0
Level 2

Following up on this one:

I/ve managed to investigate on the 'error-query" by x-debug, see the results below. This tells me that the table-order was wrong.

It seems that the search join "connects" to cck-core-table first, then constructing all joins after that...

Changing the table/field order got rid of the error... Now my results are not what I want but I guess that's a matter of rebuilding the overall join-query starting from within the cck-core-table... 

still didn't figure out why the forum resizes my images to small size (forum says "free")? bigger image at http://squareweb.nl/tmp/sebsj2.jpg

 

Get a Book for SEBLOD