MySQL Error when filtering using relation field

rgu_it_web
Topic Author
Offline
Junior Member

6 years 2 months ago #71515 by rgu_it_web

MySQL Error when filtering using relation field was created by rgu_it_web

We're receiving the following error when we try to filter content using a relation field:

Code:

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:

Code:

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.

ggppdk
Offline
Administrator

6 years 2 months ago #71525 by ggppdk

Replied by ggppdk on topic MySQL Error when filtering using relation field

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...

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

ggppdk
Offline
Administrator

6 years 2 months ago #71528 by ggppdk

Replied by ggppdk on topic MySQL Error when filtering using relation field

Hello

about the error itself
the variable at line 656

Code:

$reverse_field_id

should be replaced with

Code:

$filter->id

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

rgu_it_web
Topic Author
Offline
Junior Member

6 years 2 months ago #71537 by rgu_it_web

Replied by rgu_it_web on topic MySQL Error when filtering using relation field

Thank you, I've changed it to

Code:

$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.

rgu_it_web
Topic Author
Offline
Junior Member

6 years 2 months ago #71538 by rgu_it_web

Replied by rgu_it_web on topic MySQL Error when filtering using relation field

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.