MySQL Error when filtering using relation field

More
5 months 3 weeks ago #71515 by rgu_it_web
We're receiving the following error when we try to filter content using a relation field:
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN #__flexicontent_fields_item_relations AS rival ON rival.item_id=relv.item' at line 1

The relation and reverse relations work well within the items themselves, but when filtering we get that error. The error on the server is:
PHP Notice:  Undefined variable: reverse_field_id in \\joomla\\plugins\\flexicontent_fields\\relation\\relation.php on line 656

Looking at the code the variable $reverse_field_id doesn't appear to be getting set anywhere - could this be the problem?

Please Log in or Create an account to join the conversation.

More
5 months 3 weeks ago #71525 by ggppdk
Hello

besides the missing variable

that code is an advanced feature to allow to filter on field values not of the currently listed items

instead it filters the currently listed items, based on the the field values of their related items
and it should not be triggered unless you are using "locked filter values" parameter

are you using the above ?


-- FLEXIcontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star review. Thanks!

Please Log in or Create an account to join the conversation.

More
5 months 3 weeks ago #71528 by ggppdk
Hello

about the error itself
the variable at line 656
$reverse_field_id

should be replaced with
$filter->id


-- FLEXIcontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star review. Thanks!

Please Log in or Create an account to join the conversation.

More
5 months 3 weeks ago #71537 by rgu_it_web
Thank you, I've changed it to
$filter->id

That resolved the MySQL but I'm still unable to filter as I had hoped - I suspect I'm not setting it up/using it in the correct manner though.

Is it possible to use the relation fields to filter items within a category based on their association to their related item?

E.g. I have the below structure of schools and courses:

Items of type 'School' and in category 'Schools':
- School 1
- School 2

Items of type 'Course' and in category 'Courses':
- Course 1
- Course 2
- Course 3

I have a relation field of 'Course School' which selects from the list of items in School category + type and is assigned to the Course type. So when on a course page you see the school that the course sits within.

I have a reverse-relation field of 'School Courses' which is assigned to the School type, so when on a 'School' page you see the list of courses for that school.

I haven't enabled use 'locked' filters as I want the users to be able to select the school to filter the list of courses.

I've attached a screenshot to try and show what's currently happening.
Attachments:

Please Log in or Create an account to join the conversation.

More
5 months 3 weeks ago #71538 by rgu_it_web
Also just to clarify - I'm doing this on a category list page, not on a search page - I have just tested on a search page and it works so we may have to switch to using the search rather than filtering on category.

Please Log in or Create an account to join the conversation.

More
5 months 3 weeks ago #71556 by ggppdk
Hello

the other issue is that the
else clause is selected

Please test this fix just a few lines above
replace
if ($ri_field_id)

with
if (!$ri_field_id)


-- FLEXIcontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star review. Thanks!

Please Log in or Create an account to join the conversation.

Moderators: vistamediajoomlacornerggppdk
© 2018 Flexicontent. All Rights Reserved.