[SOLVED] Content Plugin, OR in WHERE statement

[karilint]

Hi,
I'm using the Content Plugin in an Article:

{fabrik view=list id=18 structured_name___name_id=[rownumber]}

This creates the query:

SELECT ...
FROM `relation`
LEFT JOIN `structured_name` AS `structured_name` ON `structured_name`.`id` = `relation`.`name_1`
LEFT JOIN `structured_name` AS `structured_name_0` ON `structured_name_0`.`id` = `relation`.`name_2`
WHERE ( `structured_name`.`name_id` = 548)

How can I modify the {fabrik view=list ... } to have an OR statement in the query like this:
WHERE ( `structured_name`.`name_id` = 548 OR `structured_name_0`.`name_id` = 548)

From here I can find some help: http://www.fabrikar.com/forums/index.php?wiki/filtering-lists-tables/
?tablename___A[value]=foo&tablename___A[join]=OR&tablename___B[value]=foo&tablename___B[join]=OR

But I think this is not usable in this case?
-Kari

 

[cheesegrits]

Yes, you should be able to use that syntax in the plugin, just replace the &'s with space.

-- hugh

 

[karilint]

Than you for your reply, Hugh.

It seems that my problem is that I have two relations to the same table and I'm not able to write proper query condition.

This works. I have OR condition to the same tablename (tablename___A[value]=foo ... tablename___B[value]=foo)
{fabrik view=list id=18 structured_name___name_id_raw[value]=[rownumber] structured_name___name_id_raw[join]=OR structured_name___location_id_raw[value]=1 structured_name___location_id_raw[join]=OR}

This creates: "WHERE ( `structured_name`.`name_id` = '245' OR `structured_name`.`location_id` = '1' )"

But when I try to do something like tablename___A[value]=foo ... tablename_0___A[value]=foo, the query does not work:
{fabrik view=list id=18 structured_name___name_id_raw[value]=[rownumber] structured_name___name_id_raw[join]=OR structured_name_0___name_id_raw[value]=[rownumber] structured_name_0___name_id_raw[join]=OR}

This creates: "WHERE ( `structured_name`.`name_id` = '245' )"

I'm probably making a mistake in the query condition.
-Kari

 

[troester]

Not sure about the correct syntax (WIKI is giving different ones). You can try

{ ... listname___elementname1[value][]=xxx listname___elementname2[value][]=yyy listname___elementname2[join]=OR}
or may be
{ ... listname___elementname1[value][]=xxx listname___elementname2[value][]=yyy listname___elementname2[condition]=OR}

See also http://fabrikar.com/forums/index.php?wiki/content-plugin/#embedding-lists

 

[karilint]

It must be something to do with the joining. As if the "structured_name_0" is ignored. The examples you sent are for a same list with different elements.

{fabrik view=list id=18 structured_name___name_id_raw[value]=[rownumber] structured_name___name_id_raw[join]=OR structured_name_0___name_id_raw[value]=[rownumber] structured_name_0___name_id_raw[condition]=OR}
--> WHERE ( `structured_name`.`name_id` = '245' )


{fabrik view=list id=18 structured_name___name_id_raw[value]=[rownumber] structured_name___name_id_raw[condition]=OR structured_name_0___name_id_raw[value]=[rownumber] structured_name_0___name_id_raw[condition]=OR}
--> WHERE ( `structured_name`.`name_id` or 245 )


{fabrik view=list id=18 structured_name___name_id_raw[value]=[rownumber] structured_name___name_id_raw[condition]=OR structured_name_0___name_id_raw[value]=[rownumber] structured_name_0___name_id_raw[join]=OR}
--> WHERE ( `structured_name`.`name_id` or 245 )


{fabrik view=list id=18 structured_name___name_id_raw[value]=[rownumber] structured_name___name_id_raw[join]=OR structured_name_0___name_id_raw[value]=[rownumber] structured_name_0___name_id_raw[join]=OR}
--> WHERE ( `structured_name`.`name_id` = '245' )

 

[troester]

structured_name_0

I didn't realize your setup. I thought there's a 2nd table structured_name_o (like e.g. original) joined to the 1st one (it's really hard to see a difference between 0 and o in this forum).
This is only a table alias, the DB table is still structured_name.
So what do you expect? It's all filtering the same table column "name_id"

 

[karilint]

Hi,
I have a join from table 'relation' with fields name_1 and name_2 which relate to same table 'structured_name':
SELECT ...
FROM `relation`
LEFT JOIN `structured_name` AS `structured_name` ON `structured_name`.`id` = `relation`.`name_1`
LEFT JOIN `structured_name` AS `structured_name_0` ON `structured_name_0`.`id` = `relation`.`name_2`

And I'm trying to list all rows where the field (called name_id) from the related table "structured_name" OR "structured_name_0" is [rownumber].
In current setting it only uses the first relation and does not take into account the second relation.

 

[cheesegrits]

Yeah, I don't think you'll be able to do that with the query string syntax. The syntax is pretty powerful (although a little ha does to understand!) but this is a corner case we didn't really anticipate. You might be able to achieve it with prefilters.


Sent from my HTC One using Tapatalk

 

[karilint]

Thank you troester and Hugh,

I managed to handle the query using pre-filters:
Join: WHERE
Field: Id
Condition: IN
Value: select r.id from relation r inner join structured_name sn1 on r.name_1=sn1.id inner join structured_name sn2 on r.name_2=sn2.id WHERE sn1.name_id = {rowid} or sn2.name_id = {rowid}
Type: Query

However, I run into similar (naming) problems when displaying Elements from these two relations. Only one set of Elements are displayed on the list. I probably need to make this list by making a view in MySQL and give each Element an explicit name.

 

[cheesegrits]

Ah, I didn't realize you were trying to do two list joins to the same table, thought one was a join element my brain isn't firing on all cylinders today. Nope, can't do that, as you discovered.

Sent from my HTC One using Tapatalk