How to add WHERE=ROW to LEFT JOIN?

[reidwalley]

Example: SELECT id, t1, t2 FROM table_name WHERE t3='some value'

But how do I add a WHERE condition to a LEFT JOIN? The List > Data > Joins section only allows Tables and Columns. There's no way to add a specific Row.

 

[cheesegrits]

You can't.

What are you actually trying to achieve?

-- hugh

 

[reidwalley]

I'm creating a directory of our Users. The registration process includes creating a Joomla account and filling in custom fields via JomSocial as part of the registration process. Using Fabrik, I'm trying to display bits and pieces of the User's Joomla and JomSocial data in a directory.

I want to display field_id=17, not field_id=25. But I can't figure out how to do it.

 

[troester]

Add a prefilter to your list.

 

[reidwalley]

Thank you. Pre-filter worked great for one field_id column. Now we need to display multiple JomSocial Custom Field Values (ie: field_id=17, 7, 28) in separate columns.

I found this Fabrik forum post very helpful: "Simple Question from Newbie: Need List of Joomla IDs with related table's Field value"

1. Joomla User ID
2. User Name
3. JomSocial custom profile field value (select value from jml_community_fields_values where user_id = valuefrom#1 and field_id="18")

...except how can I display multiple JomSocial community_fields_values (ie: field_id=17, 7, 28) in separate columns?

 

[cheesegrits]

If I recall how JomSocial handles the "additional fields", I don't think you can. As I recall, it uses a #_community_fields table to define the extra fields, and a #_community_fieldvalues to hold the per-user values for those fields. Which is not "joinable".

There might be some way of creating a MySQL view which has the data pivoted, so you have one row per user and each field as a separate columns, using subqueries to fetch each field columns value and grouping on the user id. Then I think you'd be able to create a one-to-one join from your Fabrik table to the view, from yourtable.userid to yourview.userid ... but I've never tried doing that, and I suspect there may be issues with joining to view, as views don't have Primary Keys (in the MySQL sense) and our join code tends to assume that one end of a given join will be the PK. Although we do have some "hail mary" code in our join handling, I haven't tested it for a while.

But ... even if you do that, it'll be a "read only" list/form, as you can't write to MySQL views.

-- hugh

 

[reidwalley]

Thank you so much for the breakdown, Hugh. I agree that the lack of PK on views is gonna be the downfall.

 

[cheesegrits]

Well, it may be worth trying, if all you need is a "read only" list of the data. When you create the view, you would be creating one row per user ID. So that would be a unique id. You could then create a Fabrik list from that view (which we support), and configure that user ID field as the "PK". That part we know works - our code doesn't care if what you designate as the PK on a List created on a view is really a PK - it's just a unique id we can use as such for selecting rowids.

Normally you wouldn't need to create a Fabrik list on a table in order to join it to another one, but in this special case, that's how my "Hail Mary" in the join code works. If there is no "real" PK, I look in our list metadata to see if the view is known to us as a list, and if so, use what you designated as the PK for that list.

So I think this would work. I can't promise it, but ... worth a go.

Are you familiar with how to set up views in MySQL, and how to use dependent subqueries in it?

-- hugh

 

[reidwalley]

That sounds cool.

Unfortunately, I am not familiar with how to set up views in MySQL, and how to use dependent subqueries in it.

Is this a good place to start? - Fabrik's Wiki: Using MySQL Views

 

[cheesegrits]

Well, the main issue will be building the query that creates row data you need. Off the top of my head, the query would look something like this:

select
   fv.user_id,
   (select ifv.value from jos_community_fields_values as ifv where ifv.field_id = '2' and ifv.user_id = fv.user_id) AS field_2,
   (select ifv.value from jos_community_fields_values as ifv where ifv.field_id = '17' and ifv.user_id = fv.user_id) AS field_17
from jos_community_fields as f
left join jos_community_fields_values as fv ON fv.field_id = f.id
group by fv.user_id

... would get you a view with "user_id, field_2, field_17". Which you could then add as a List, designate user_id as the "PK", then make a join from your main table to it, as main_table.user_id => view_name.user_id

You can run that query by hand in phpMyAdmin, then when it's working, prepend it with "CREATE VIEW yourviewname AS " and run that to create the view.

This is from memory of the JomSocial table structure, which may have changed, plus you'll need to change the jos_ prefix to your table prefix.

-- hugh

 

[cheesegrits]

Oh, and note that correlated subqueries can be notoriously slow, and of course a view is slower than a table anyway )as it's really just a query creating a temporary table ... so if you have a lot of users, and are calculating a lot of those field columns ... it could be rather slow. Although MySQL is at least smart enough to cache views until any of the data involved changes.

-- hugh

 

[reidwalley]

Thanks. This gives me a great place to start.

 

[reidwalley]

Running the following SQL by hand in phpMyAdmin (with the correct table prefix):

SELECT
fv.user_id,
(SELECT ifv.value FROM cho36984_community_fields_values AS ifv WHERE ifv.field_id = '2' AND ifv.user_id = fv.user_id) AS field_2,
(SELECT ifv.value FROM cho36984_community_fields_values AS ifv WHERE ifv.field_id = '17' AND ifv.user_id = fv.user_id) AS field_17
FROM cho36984_community_fields AS f
LEFT JOIN cho36984_community_fields_values AS fv ON fv.field_id = f.id
GROUP by fv.user_id

...I get the following error: #1242 - Subquery returns more than 1 row

I'm trying to work out what that SQL error means. The JomSocial table column titles are all correct.

 

[cheesegrits]

It means that your fields_values table has more than one row for a given user_id / field_id combination, which I don't think should happen.

Try doing ...

SELECT * FROM jos_community_fields_values WHERE field_id IN ('2', '17')

... and see if you can spot where that's happening. There should only be one row per combination of user and field.

-- hugh

 

[cheesegrits]

Alternatively you could just assume that for any duplicates, the most recent one is the one to use, and just add an "ORDER BY fv.id ASC LIMIT 1" to those sub queries.

-- hugh

 

[reidwalley]

Ah, yes, "duplicates" makes sense. Ugh. Yes, there are at least two listings per JomSocial User because of the tie-in with their PayPlans annual membership renewal process.

Thanks for the insight and the extra code.

 

[cheesegrits]

Does adding the ORDER BY and LIMIT help?

-- hugh

 

[reidwalley]

Yes, the "ORDER BY fv.id ASC LIMIT 1" code works great. And you're right, it loads slowly

 

[cheesegrits]

Yup. Not a lot you can do about that really, I don't think there's any optimization you can do. Although I'm only about a brown belt on MySQL, if you went over to Stack Overflow, and asked if there's a more efficient way of doing this ... there are some Ninja level black belt MySQL folk over there who may have some suggestions.

I finally dug up the database I have with JomSocial installed, and they already have indexes on the fields you are using, so nothing to gain by adding any indexes.

-- hugh

 

[reidwalley]

Thanks, again, for all your help, Hugh. Really, it's been a giant help to me! I'll head over to Stack Overflow to see if there are recommendations for optimization.