Indent Child Elements from Parent Elements in Dropdown

[pkjoshi]

I have created a dbjoin element using self join for creating categories & sub-categories. This is working fine.

Only thing I want that the items(category) in drop-down be indented to the right from the parent.
Need some help how I can achieve this?

I am attaching a image here for more clarity.

 

[rob]

perhaps you could achieve that by using a concat label on the dbjoin element. Presumably there is something that will identify a record as a parent or child record so you could use a mySQL "IF" or similar logical structure to construct strings for the labels as either 'Label' for the parent entries, or ' -- Label' for the child entries.

 

[pkjoshi]

Thanks I will have a go at it the way you have suggested.

Sent from my GT-I9082 using Tapatalk

 

[pkjoshi]

Hi Rob,
As per your suggestion I have build up a query which works fine in phpmyadmin, but I am struggling to get it work in fabrik join element.
I have attached the snapshot of the detailed settings.
But I am getting the error like this:

getData:Unknown column 'p.path_length' in 'field list' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `categories`.`id` AS `categories___id`, `categories`.`id` AS `categories___id_raw`, `categories`.`date_time` AS `categories___date_time`, `categories`.`date_time` AS `categories___date_time_raw`, `categories`.`category` AS `categories___category`, `categories`.`category` AS `categories___category_raw`, `categories`.`parent` AS `categories___parent_raw`, CONCAT_WS('', CONCAT(REPEAT('-', p.`path_length`), d.`category`)) AS `categories___parent`, `categories`.`id` AS slug , `categories`.`id` AS `__pk_val` FROM `categories` LEFT JOIN `categories` AS `categories_0` ON `categories_0`.`id` = `categories`.`parent` LIMIT 0, 10

 

[cheesegrits]

It doesn't appear to have picked up your additional joins. And looking at my code where I added this feature, it seems you HAVE to specify the join type, you can't use "JOIN" by itself:

/*
* Set up RE of all possible valid MySQL join types, as per join_table spec here:
* http://dev.mysql.com/doc/refman/5.0/en/join.html
* EXCEPT just 'JOIN' by itself.  Don't ask.
*/
$re = "(LEFT\s+JOIN)" .
"|" .
"(LEFT\s+OUTER\s+JOIN)" .
"|" .
"(RIGHT\s+JOIN)" .
"|" .
"(RIGHT\s+OUTER\s+JOIN)" .
"|" .
"(INNER\s+JOIN)" .
"|" .
"(CROSS\s+JOIN)" .
"|" .
"(STRAIGHT_JOIN)" .
"|" .
"(NATURAL\s+JOIN)" .
"|" .
"(NATURAL\s+LEFT\s+JOIN)" .
"|" .
"(NATURAL\s+RIGHT\s+JOIN)" .
"|" .
"(NATURAL\s+LEFT\s+OUTER\s+JOIN)" .
"|" .
"(NATURAL\s+RIGHT\s+OUTER\s+JOIN)";

So try specifying LEFT JOIN instead of just JOIN.
-- hugh

 

[cheesegrits]

(If that fixes it, I'll update the tooltip on that feature to say that you have to specify a join type)

 

[pkjoshi]

Thanks Hugh,
I update the query as per your suggestion. It works perfect in the form, but it still throwing error in the list.

getData:Unknown column 'p.path_length' in 'field list'
SQL=SELECT DISTINCT `categories`.`id` AS `categories___id`,
`categories`.`id` AS `categories___id_raw`,
`categories`.`date_time` AS `categories___date_time`,
`categories`.`date_time` AS `categories___date_time_raw`,
`categories`.`category` AS `categories___category`,
`categories`.`category` AS `categories___category_raw`,
`categories`.`parent` AS `categories___parent_raw`,
CONCAT_WS('', REPEAT('-', p.`path_length`),
categories_0.`category`) AS `categories___parent`,
`categories`.`path_length` AS `categories___path_length`, `categories`.`path_length` AS `categories___path_length_raw`, `categories`.`crumbs` AS `categories___crumbs`, `categories`.`crumbs` AS `categories___crumbs_raw`, `categories`.`id` AS slug , `categories`.`id` AS `__pk_val` FROM `categories` LEFT JOIN `categories` AS `categories_0` ON `categories_0`.`id` = `categories`.`parent` WHERE `categories`.`id` IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15') AND `categories`.`id` IN ('1','2','3','4','5','6','7','8','9','10')

Settings in my join element are like this:
Concat Label

REPEAT('-', p.`path_length`), {thistable}.`category`

Joins where and/or order by statement (SQL)

WHERE p.`ancestor` = 1
GROUP BY {thistable}.`iD`
ORDER BYGROUP_CONCAT(crumbs.`ancestor`)

Additional join statements

LEFT JOIN cat_closure AS p ON {thistable}.`id` = p.descendant
 
LEFT JOIN `cat_closure` AS crumbs ON crumbs.`descendant` = p.`descendant`

 

[rob]

oh dear The additonal join statements are not used in the list view hence the error. Not sure what we can do about that I'll have to discuss it with Hugh
Perhaps a work around would be to create a mySQL view of the data you need and link the database join element to that?

 

[pkjoshi]

Thanks Rob,
Not sure I understand it completely what you suggest.
Do you mean that I create a mysql view and on form save it is redirected to the list created by mysql view. (looks quite possible).
But how do you add a new record from a list view using the add record icon or for that matter edit a record showing in the list?

 

[pkjoshi]

Nop, It's not working.
I am able to redirect the form to the list created from mysql view.
But for editing record even though I tried creating the custom link for editing record using usekey, it throws the same error and presents a blank form.

What next Rob, Hugh?
I can't move an inch beyond that i.e. I can add record fine, but can't edit or delete record.

 

[cheesegrits]

I'll see if I can see what the issue is in list view ... but to be honest, this is somewhat pushing the envelope, with self-joins, and using the concat to produce indented labels using additional joins ... really not the kind of thing we had in mind when we created this element / options ... and to be honest, somewhat outside the scope of "Standard" support.

I'm not sure how you created your view, but I think what Rob had in mind was to create a view which handles inserting the indents at the MySQL level, so you don't have to do the concat / additional joining thing in Fabrik.

-- hugh

 

[pkjoshi]

Thanks Hugh.
I am not averse to subscribe to professional support if I get the support for the solution I need for this project.(Refer my conversation I started with you in this regard - Business Directory With Fabrik - Oct -19) I am still waiting for your response.
What I am trying to do, what I understand is within the scope of options provided in there i.e. using additional joins which is not picked by list as Rob pointed out.
So I feel I am not asking you for any feature request here which is outside the scope and I am just expecting the options to work as it is expected to work. Please forgive me if I am wrong here.

However as far as this part of the problem I hope I will be able to solve using mysql triggers i.e. I will create a table from mysql view and then update it using php form submission.

But I am still interested for professional support on your response to my conversation with you.
Thanks once again.