Databasejoin And Alias Self-Joins [SobiPro Integration]

[sgorney]

Hi All:

I've got a oddball here. I'm attempting to create a contact form which has a checkbox list of SobiPro listings. The general idea is that a user will select checkboxes of all the listings they are interested in, so that additional information can be sent to them.

SobiPro has a data structure that makes it a bit hard to just do a typical databasejoin + concat of other fields. The reason is because all the data is stored in one table like this:

sobipro_field_data
-record 1
--baseData "ListingName"
--fid "1"
--sid "1"

-record 2
--baseData "Listing City"
--fid "2"
--sid "1"

You can see that the sid field is used to relate these two records, to become "one" record later on for output. This is smart way of doing things, but unfortunately I hit a wall when trying to use Fabrik on top of that.

This SQL query works great when I'm using it in PhpMyAdmin. It's a self-join to relate the records using aliases:

SELECT
name.fid as nameFID,
name.baseData as name,
city.baseData as city,
city.fid as cityFID
FROM k7p8q_sobipro_field_data as name left join
k7p8q_sobipro_field_data as city on name.sid = city.sid
WHERE name.fid = 1 AND city.FID = 2

I'm hoping to find a way to get the same effect using the databasejoin, or perhaps another alternative. I need to get three pieces of data from this table and display it as a "single" checkbox for the user (listing name, city, and a photo).

Is this going to be possible? Or should I start looking at making a custom fabrik element to handle it?

-Chad

 

[sgorney]

Friendly bump

I've kind of come to the conclusion that this won't be possible with the dbjoin element. So I started down the road of a custom element plugin. I was able to figure out the XML easy enough and get fabrik to recognize it in the element list.

Here's my sobipro.php file. I'm struggling with two things:

1) How exactly should I be extending the plgFabrik_Element class to display a checkbox group? I know I will be using my above query to build an object with values and labels for the checkbox, am I right to assume I will be overriding the render() function to loop through that object and build the checkboxes?

2) I'm struggling with getting / sending the selected value(s) to be stored in the database. What variable should I be setting (I thought $data but that doesn't seem to work) so that fabrik will store my value to the DB.

Here's a gist of my sobipro.php plugin file. Right now I'm just trying to proof-of-concept with some checkboxes to get data storing:

https://gist.github.com/3609328

Any assistance on getting this done right would be super helpful! Thanks all.

-Chad

 

[rob]

Sorry I missed the first question, to that I would answer, yes it is possible you would create a mySQL view from that query (or a variant of that query) and then use that to build your database join element.

I'd see if that works before answering the other questions.

Otherwise for the class the first thing I would change would be to extend from either the database join or checkbox element (depending on how you need the data stored) rather than the base element model. This would get you a long way down the track to getting / storing the data as desired.

 

[sgorney]

Sorry I missed the first question, to that I would answer, yes it is possible you would create a mySQL view from that query (or a variant of that query) and then use that to build your database join element.

Are you saying I can do this using Fabrik's off the shelf db join element? If that's possible I think I'd rather do that. Problem is I'm not exactly sure how to go about doing it. What would be the way to implement a self-join in the db join element?

 

[rob]

In PHP my admin, use a variant of the query you posted:

SELECT
name.fid as nameFID,
name.baseData as name,
city.baseData as city,
city.fid as cityFID
FROM k7p8q_sobipro_field_data as name left join
k7p8q_sobipro_field_data as city on name.sid = city.sid
WHERE name.fid = 1 AND city.FID = 2

I've made a tutrial explaining the process which you can see here:
http://fabrikar.com/help/tutorials/details/3/30
You will need to log in to see it

 

[sgorney]

Hi Rob:

Excellent tutorial! I've created a mysql view. I'm trying to do a concat label, and I"m getting this error:

Unknown column 'tbl_sobi_fab.name' in 'field list' SQL=SELECT DISTINCT(`sign_up_repeat_select_entries`.`name`) AS value, CONCAT(tbl_sobi_fab.name,' - ',tbl_sobi_fab.city) AS text FROM `tbl_sobi_fab` AS `sign_up_repeat_select_entries` ORDER BY text ASC

I'm not sure what the sign_up_repeat_select_entries is coming from, I'm wondering if it's a left over vestage of another element. I'll try recreating the form and see...

 

[sgorney]

Okay so, there might possibly be a bug here. If I set the DB Join element to 'dropdown', my concat loads fine. If I set it to checkbox, I get the same error as above.

Here's what I'm loading in the concat area:

tbl_sobi_fab.name,'<div class="box">',tbl_sobi_fab.city, '</div>'

Again, this works (minus the HTML because it's a dropdown) in the dropdown, but gives this error:

Unknown column 'tbl_sobi_fab.name' in 'field list' SQL=SELECT DISTINCT(`new_form_repeat_dbjoin`.`name`) AS value, CONCAT(tbl_sobi_fab.name,'
',tbl_sobi_fab.city, '
') AS text FROM `tbl_sobi_fab` AS `new_form_repeat_dbjoin` ORDER BY text ASC

When setting to checkbox. My hope is that I can create a pretty custom looking checkbox label (complete with images for styling).

A look at the above error that only comes up in checkbox mode of the dbjoin element would be great!

-Chad

 

[cheesegrits]

Try using {thistable} instead of a specific table in your naming, so for instance, {thistable}.name. I'm not sure if that will fix this, as the query doesn't look quite right anyway, but it's definitely something you need to do. When building join statements, we often give the joined table an alias, using "AS foo", and that's the name you need to use in your CONCAT naming. But as you don't know in advance when that alias will be, we provide the {thistable} placeholder, which we replace with the table alias being used at run time.

-- hugh

 

[sgorney]

Hi Guys:

I replaced using the {thistable} placeholder, and I get this error when viewing with the checkbox option:

getData:Unknown column 'sign_up_repeat_select_entries.name' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `sign_up`.`id` AS `sign_up___id`, `sign_up`.`id` AS `sign_up___id_raw`, `sign_up`.`date_time` AS `sign_up___date_time`, `sign_up`.`date_time` AS `sign_up___date_time_raw`, `sign_up`.`Name` AS `sign_up___Name`, `sign_up`.`Name` AS `sign_up___Name_raw`, `sign_up`.`email` AS `sign_up___email`, `sign_up`.`email` AS `sign_up___email_raw`, `sign_up`.`password` AS `sign_up___password`, `sign_up`.`password` AS `sign_up___password_raw`, (SELECT GROUP_CONCAT(CONCAT(sign_up_repeat_select_entries.name,' - ',sign_up_repeat_select_entries.city) SEPARATOR '//..*..//') FROM sign_up_repeat_select_entries LEFT JOIN tbl_sobi_fab ON tbl_sobi_fab.name = sign_up_repeat_select_entries.select_entries WHERE sign_up_repeat_select_entries.parent_id = `sign_up`.`id`) AS sign_up_repeat_select_entries___select_entries, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM sign_up_repeat_select_entries WHERE parent_id = `sign_up`.`id`) AS `sign_up_repeat_select_entries___select_entries_raw`, (SELECT GROUP_CONCAT(select_entries SEPARATOR '//..*..//') FROM sign_up_repeat_select_entries WHERE sign_up_repeat_select_entries.parent_id = `sign_up`.`id`) AS sign_up_repeat_select_entries___select_entries_id, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM sign_up_repeat_select_entries WHERE parent_id = `sign_up`.`id`) AS `sign_up_repeat_select_entries___params`, `sign_up`.`id` AS slug , `sign_up`.`id` AS `__pk_val` FROM `sign_up`

When I set to a dropdown, I don't get any error and the element is rendered as expected. I tried this in my concat field:

{thistable}.name,' - ',{thistable}.city

Again, works great in dropdown, does *not* work in checkbox.

 

[jcc]

Could it be that the view column name seems to be "Name" and you are trying to concat "name"?

 

[sgorney]

Hi:

Nope the column name is "name". The table that I"m accessing in phpMyAdmin has a column titled "tbl_sobi_fab.name", and the /form/ has an element titled sign_up___Name. They are different fields altogether.

This makes me think that perhaps {thistable} isn't the correct usage here? I'm not accessing "this" fabrik table. I'm accessing a different table in the database.

Anyway, I do need to get this resolved.

-Chad

 

[sgorney]

Friendly bump on this. Looking to try anything to get the concat working correctly. Right now I've been running this on a localhost, but I'm going to be setting it up on a live dev environment as I'm going to be working on other parts of this, so I can provide a live site link in a few days.

 

[cheesegrits]

I'm going to need to have access to the system to get any further with this, I just can't get a clear picture of what you are trying to do without actually seeing it.

-- hugh