Filter on list

[Glynneth]

Hello.

I'm trying to setup a Prefilter on a list, comparing a value taken from an element of the current logged-in user's record (in the 'Value' field) and comparing this with a value from the 'WHERE' clause. Thought I had correctly followed the guide from the wiki, but it isn't working. Here is the PNG image from the admin page:

Thanks.

 

[troester]

Not sure about your setup.
A "Type"=Eval value must be php code returning something and {$this->users__id->level...} is nothing Fabrik knows.

Assuming your "current logged-in user's record" is in a table myusertable with columns level_of_tuition and userid you could do something like
value:
SELECT level_of_tuition FROM myusertable WHERE userid={$my->id}
Type = Query

 

[Glynneth]

I am trying to compare equivalent element values between a record from the "Available Jobs" list and the list record of the current logged-in user. This is to try and match up suitable records for display to the current user.

 

[cheesegrits]

In which case, you certainly need a sub-query, as per Troester's response. Select the 'level of tuition' from the other table, using {$my-id} as the placeholder to match against the user field in the other table.

Is there something specific about Troester's answer you aren't clear on? It's example query, you'd need to change the table and field name(s) accordingly, but that should be straight forward. Assuming we understand you correctly, and you have another table which has the user ID and the "level of tuition" in it, simply replace the field and table names in that example query.

-- hugh

 

[Glynneth]

OK, I will try and explain in plain English, as not sure what is going on above. I don't want to match user ID's, I am looking to match an element's value from the current logged-in user ('level_of_tuition') with another equivalent element's value from another table/list ('tuition_level'), both represent tuition levels. I am trying to compare between these two elements only, not user ID's. Thanks.

 

[troester]

No user ID's are compared.
If you want to compare one element ('level_of_tuition') against an element from an other list ('tuition_level') you have to fetch this 2nd element with a MySQL query
SELECT tuition_level FROM your-other-table WHERE some-condition

You said the tuition_level is depending on the logged_in user, so I assume the condition is
WHERE userid-in-your-other-table = {$my->userid}
but this is depending on your setup and has be filled by yourself.
In any case the query must return one single value.

 

[Glynneth]

OK, I think I have got it sorted now regarding the implementation. However, there the list is still not showing any records when I apply the following Prefilter. The list does display the records without a Prefilter, so I know the list generally is fine. Here is the configuration for you to take a look at:

 

[troester]

Goto Fabrik Options, set "allow fabrikDebug"=YES (Debugging tab).
Display your list, append &fabrikdebug=1 (or ?fabrikdebug=1 if there's no other parameter) to the URL.
A click on "list GetData" will show you the generated query.

Is "Apply to" set to what you need?

 

[Glynneth]

Right, I am homing in on the problem here. Firstly, in the Tutor List there are no proper IDs being created for some reason, as shown in the following Print Screen:

When I next click to edit each record, I get the following error message at the top of the screen:

Here is the Print Screen for the Debug:

Also, I notice that it seems to be comparing a standard Joomla! user ID with the Fabrik record creation ID (Tutors List), is this also what is happening from what you can see?

 

[troester]

It seems there's something screwed up in your setup.

Should Tutor_ID be the primary key of your list? It surely is not (can't be NULL and must be unique).
If an element is storing the user_id it (usually) can't be the primary key because the PK is autoincrement (in a standard Fabrik list/table).

Maybe the problems are related to your other thread.

Main points:
a standard Fabrik list has a primary key (usually called id), autoincrement, type internal id

in all Fabrik joins one join element must be a PK, the other one must NOT be a PK (i.e. it's a foreign key FK, typically a field or a dbjoin to the other list)
i.e. the join must be "from tableA.id to tableB.some-element" or "from tableA.some-element to tableB.id", it can't be "from tableA.id to tableB.id" or "from tableA.some-elementA to tableB.some-elementB"

In a standard Fabrik setup nor the PK nor the FK can be a user element, because the user element is storing a Joomla user id, PK (and a joined FK) are storing Fabrik internal IDs.

So what are your join settings, what are your elements...?

 

[Glynneth]

I have made some progress by changing the name (not the label) of the 'internalid' Plug-in back from 'tutor_id' to simply 'id' and this has sorted out the Tutors List as follows:

The second problem I had when I clicked on edit and it produced the 'unable to load JUser' problem was only happening on the first record in the list, the rest are fine, so it looks like it is record-specific. What do you think?

However, the problem still remains in that there are no records showing when the Pre-filter is applied. As mentioned before, the list displays all the records fine without the Pre-filter, so I guess it is specific to it. Any ideas?

Have checked the Foreign Key - Primary Key links + settings and they are fine. Thanks.

 

[Glynneth]

Hello. Was wondering if someone has missed my last post? Ta.

 

[troester]

How is the debug query looking now (with the prefilter)?
Is there any record matching your condition?

 

[Glynneth]

Yes, there is a matching condition as the following Print Screen shows. As I mentioned in an earlier post, I notice that it seems to be comparing a standard Joomla! user ID with the Fabrik record creation ID (Tutors List), is this also what is happening from what you can see here?

I think the other possible reason for the non-display could be something to do with the elements being compared. See the following Print Screen of the Checkbox elements (Level of tuition) used for comparing the values, is the same in both compared lists:

What do you think? Thanks.

 

[troester]

I notice that it seems to be comparing a standard Joomla! user ID with the Fabrik record creation ID (Tutors List),

This is exactly what you have defined in your prefilter, it's up to you to create the correct condition.
I don't think the id element in tutoring contains the userID.

As I've said in post#2 you must use the element which contains the userid (I don't know your setup), so something like
SELECT level_of_tuition FROM tutoring WHERE your-userid-element={$my->id}

 

[Glynneth]

For the part blue-highlighted in the following statement: SELECT level_of_tuition FROM tutoring WHERE your-userid-element={$my->id}. I have now used the Fabrik User element (this_user_id), set to 'ID' for 'User Data' in the 'Options' tab. It is is storing the Joomla! User ID fine, as I have checked in the list display. So my statement reads:
SELECT level_of_tuition FROM tutoring WHERE this_user_id = {$my->id}

I know it is now comparing "like for like" on IDs, but it still isn't displaying any records in the list, so I'm looking towards the elements being compared themselves, i.e. the Checkbox element: 'level_of_tuition'. The values entered for this element are displayed in my previous post for you to see. Do you think there could be a problem here?

 

[cheesegrits]

If you run just that subquery in phpMyAdmin (or whatever), how many values does it return?

By which I mean, copy an example subquery (the "SELECT level_of_tuition ..." but) from the getData debug output which you know should produce results, and paste and run that in phpMyAdmin by hand.

-- hugh

 

[troester]

Additionally:
I'm not sure if a LESS condition is working with a checkbox element.
What do you expect to get? In a checkbox element you can check multiple (or non) values.
Why don't you use a radiobutton?

 

[cheesegrits]

troester - yup, that's why I'm interested in seeing what that query actually returns. It'll probably be JSON format, like ["1"]. Whereas I think a radio button would return a simple value.

-- hugh

 

[Glynneth]

Know where you are coming from with the checkbox problem, so have changed to radio button now. However, there is still no records showing in the front end list and this error when I click on 'View data' in the admin back end: