Table Join Help

[angiras]

I've got a weird problem and I've been trying to solve it for a few days and I'm just out if ideas ... I know how I'd solve it in straight PHP ... but not how to solve it in Fabrik ... I'm fairly certain it can be done with a table join, but MySQL joins are not my strong suite ... so lets see....

Here is what I've got ...

2 tables for a 'event registration' setup.

Table 1 is a list of all events.

Table 2 is a list of users and the ids of the events in table 1 they signed up for.

I need to display only the events the user has not signed up for.

Note this is on a J 1.0.x site using Fabrik 1.0.6

Structure of table 1 is :

eventid, eventname

Structure of table 2 is :

id, userid, eventid

-----------------------
Hopefully someone has an idea...?

 

[cheesegrits]

I'm pretty sure we added the 'subquery' type for table pre-filters in 1.0.6, might only be in SVN tho.

Check you pre-filter GUI on the back end (edit the table, under the Data table) and see if your have 'subquery' (or maybe 'query') under the Type dropdown for the Value.

If so, then you can do something like this:

WHERE
Field: eventid
Condition: NOT IN
Value: SELECT eventid FROM table1 LEFT JOIN table2 ON table1.eventid = table2.eventid WHERE table2.userid = {$my->id}
Type: query

I think. Basicaslly you need to craft that Value query so it returns the events that user has. You may have to mess around in phpMyAdmin running test queries to get it just right.

-- hugh

 

[angiras]

Under the Pre-Filter there is a Value(eval) box with a series of radio buttons under it labeled "eval: yes no subquery" ...

So I'll try that.

It would be nice if there was a way to see the resulting MySQL query that fabrik created after you put in your filter and stuff ... it would be a big help in figuring out what to do or not to do ... is there a way to get that to display by editing the template or something?

Thanks for the help.

 

[cheesegrits]

Yes, append &fabrikdebug=1 to your URL.

-- hugh

 

[angiras]

Okay ... I haven't gotten it to work yet, but I figured out that the MySQL query I need to make it work is :

SELECT * FROM table1 WHERE eventid NOT IN (SELECT eventid FROM table2 WHERE userid = 'userid')

I tried creating a query :

field : eventid
condition : NOT IN
value : SELECT eventid FROM table2 WHERE userid = '{$my->id}'
eval : Subquery

Which I assume creates the above query, but for some reason it doesn't work.

 

[angiras]

I got this to work.

The problem was a stupid mistake. I wasn't setting the 'apply filter to' high enough. Oops.

Otherwise, what was in my last post worked great.

NOTE : In case it isn't clear - I have *NO* table joins in this setup. Standard table view with subquery filter was all that I needed to do.

Thanks for your help.