Filtering repeat rows in a form

[tali]

Hi,

My form is about books.
On each book form, selected readers will post appreciations on three aspects of the book.
Before appreciations are made available to others by the administrator, we want the reader to see only his own appreciations.

The following a query for the form would do the job for the book 124 read by the user 249 with the three evaluations (tables 74, 75, 76) on the three aspects of the book:
SELECT b.*, ev74.comment comment74, ev74.de de74, ev75.comment comment75, ev75.de de75, ev76.comment comment76, ev76.de de76
FROM fk__books b
LEFT JOIN fk__books_74_repeat ev74 ON ev74.parent_id = b.id AND ev74.de = 249
LEFT JOIN fk__books_75_repeat ev75 ON ev75.parent_id = b.id AND ev75.de = 249
LEFT JOIN fk__books_76_repeat ev76 ON ev76.parent_id = b.id AND ev76.de = 249
WHERE b.id=124
It will show the book's fields and the evaluations the reader has (or not) already given.

1) Is it possible to tell the form to use the above query instead of the built-in one?
The standard query is, I suppose,
SELECT b.*, ev74.comment comment74, ev74.de de74, ev75.comment comment75, ev75.de de75, ev76.comment comment76, ev76.de de76
FROM fk__books b
LEFT JOIN fk__books_74_repeat ev74 ON ev74.parent_id = b.id
LEFT JOIN fk__books_75_repeat ev75 ON ev75.parent_id = b.id
LEFT JOIN fk__books_76_repeat ev76 ON ev76.parent_id = b.id
WHERE b.id=124
This standard query will show also the evaluation of the other readers, which, at this stage, is not wanted.
We want to see only the user 249's evaluations.


2) Or is there a way to pre-filter the rows (on {$my->id}) in the menu Fabrik settings in order to show the reader only the rows he has (or will) set?

 

[troester]

Add prefilters on the joined elements in your books list.
They are applied in form/details view also.

 

[tali]

Thank you for your answer.
This is what I added to a Pre-Filter showing a first level selection (=124 in the example) :
AND fk__books_74_repeat.de EQUALS 249 Grouped=yes

I also set to Yes the fk__books_74_repeat for Lists and Queries

The result is that no books are shown anymore in the list, even not the one with an already added evaluation for book 124 with de=249.

The goal is to see in the list
- All the books (pre-filtered by a first level selection),
- and for those books only the evaluations of the user.

Books not yet evaluated have to be shown either in the list in order to allow the user to give his/her evaluation.
He/She must no see the others evaluations.

Quering directly
SELECT DISTINCT b.id, ev74.comment comment74, ev74.de de74
FROM fk__books b
LEFT JOIN fk__books_74_repeat ev74 ON ev74.parent_id = b.id
WHERE b.id = 124
AND (ev74.de = 249 OR ev74.de IS NULL)
gives the right row,
but the pre-filter above doesn't show it in the list.

 

[cheesegrits]

It would probably help if you enabled Fabrik debug in your global settings, and appended &fabrikdebug=1 to the page URL, so you can see what query Fabrik is actually generating.

-- hugh

 

[tali]

Good hint : there was an error on the field joined to the user. Using id_raw instead of _id gave the right query.

However, I still have an issue.
- The user who is going to give evaluations arrives on a list.
- This list shows all the books he/she may evaluate.

If I apply on the list the pre-filter also on repeat evaluations (to show only his/her evaluations), he/she will not see in the list the books he/she has not yet evaluated.

So, in fact, there is a first pre-filter for the list : books which are in the process of evaluation

and a second filter when showing the form, to apply on the evaluations (to show only his/her evaluations).

Is it a good idea to try to achieve this by Edit URL customization in the list parameters?

 

[cheesegrits]

I'm a but confused by your setup, and can't really devote the time to getting my head round it as a Community issue. But I'm assuming you could do this with another pre-filter, using OR and grouping it to the first one.

-- hugh

 

[tali]

Thank you for your answer. I'll try what you say.