Welcome, Guest

TOPIC: [ADDED] Filters on pivots

Filters on pivots 08 Aug 2016 18:37 #14387

MorganL
Offline
Platinum Member
Posts: 438
Thank you received: 53
Karma: 16

Only just getting back into using the builder properly again for a large component, and last time i spent major time with it, the pivot table facility had just been put in (easy to use, works great).

I notice that the filters using pivot tables still cannot be built. Are we any closer to getting these in place OR do you have a code snippet on GIT or jotted down that I can use to build this filter in my model. i want to use a pivot table for

Job
Skills

Pivot Job <-> Skills

I then want to be able to filter the jobs based on having a certain skill

I assume it would be a where statement querying a 2 level join, or maybe a subquery though not generated any component yet to have a play with the tech

ie the rendered query would be

SELECT a.*
FROM #__mycomponent_jobs AS a
WHERE a.id IN (
  SELECT c.id
  FROM #__mycomponent_pivotjobskills c LEFT JOIN #__mycomponent_skills b ON c.skill_id = b.id
  WHERE b.skill LIKE '%filter_skill_chosen%')

Morgan Leecy MCSE

Novell / Linux
PHP. MYSQL, Apache, node.js
Coldfusion, JQuery, HTML5
Joomla

The administrator has disabled public write access.

Filters on pivots 08 Aug 2016 18:55 #14388

MorganL
Offline
Platinum Member
Posts: 438
Thank you received: 53
Karma: 16

of course building the query is one thing, but would be nice to have a nice combo box that ONLY propegates with (as per my example) only the skills that have been used at some point

i,e the list is not pulled from #__mycomponnet_skills but is pulled from

SELECT DISTINCT (b.skills)
FROM#__mycomponent_pivotjobskills a
LEFT JOIN #__mycomponent_skills b ON a.skill_id = b.id
GROUP BY b.skills

for example as there is no way in the builder to make tis filter yet

Morgan Leecy MCSE

Novell / Linux
PHP. MYSQL, Apache, node.js
Coldfusion, JQuery, HTML5
Joomla

The administrator has disabled public write access.

Filters on pivots 08 Aug 2016 19:00 #14389

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	That's a very good proposal. With time... hold patience because I have a lot to do before, but yes of course that's a minimum. If cook proposes N:m, it should be able to propose N:m filters. Added to the list
	Coding is now a piece of cake The administrator has disabled public write access.

Filters on pivots 16 Aug 2016 12:58 #14448

organicwebs Offline Premium Member Chris Posts: 133 Thank you received: 21 Karma: 4	+1 on this. We cannot fully use your good work on Pivot/Join tables if we cannot use the filters. Essentially it means we cannot filter any multi- selection field.
	Just call me Chris The administrator has disabled public write access.

Filters on pivots 14 Oct 2016 01:48 #14641

organicwebs Offline Premium Member Chris Posts: 133 Thank you received: 21 Karma: 4	Hi Jocelyn , just wondering if we can have filters for pivot/join tables? I really need it for a project I'm working on. Cheers!
	Just call me Chris Last Edit: 14 Oct 2016 01:52 by organicwebs. The administrator has disabled public write access.

Filters on pivots 23 Nov 2016 04:43 #14723

organicwebs Offline Premium Member Chris Posts: 133 Thank you received: 21 Karma: 4	Any news on this?
	Just call me Chris The administrator has disabled public write access.

Filters on pivots 23 Nov 2016 17:34 #14737

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	It is some challenge I can try for next monday. I don't promise it, but let's see... At least, I will start to dig in. Maybe not so complex... The framework is so powerfull, that some lines of code on the correct target place... should not be a big deal.
	Coding is now a piece of cake The administrator has disabled public write access.

Filters on pivots 28 Nov 2016 10:56 #14752

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	Hold on your breath until tonight... lol
	Coding is now a piece of cake The administrator has disabled public write access.

Filters on pivots 28 Nov 2016 11:11 #14753

organicwebs
Offline
Premium Member
Chris
Posts: 133
Thank you received: 21
Karma: 4

Hey Chef - thankyou so much for looking into this

Sorry for nagging - I needed it desperately for a compoment - so much so I was going to rewrite my component tonight, removing the Pivot tables and replacing with Booleans because of a need for filters. I'm using the Pivot table as a solution for a Muliselection ENum.
But with this good news, I can hold off for a few more days. Super glad you are working on it, it's going to be a powerful addition

Just call me Chris

The administrator has disabled public write access.

Filters on pivots 28 Nov 2016 11:14 #14754

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	It is already uploaded. Just need to step the version and write some explanations
	Coding is now a piece of cake The administrator has disabled public write access.

Filters on pivots 28 Nov 2016 11:30 #14756

admin
Offline
Administrator
Chef
Posts: 3711
Thank you received: 984
Karma: 140

Ok, you can try it in exclusivity. Beta tester, lol.

Instance the new filter called 'List',
Then, drop inside a N:m table relation :
- From the fields tree of your table (ex:Cities),
- Open a Xref node (ex : Visits), it should be a Pivot table
- Drag a table node (ex: Travellers), it is the Foreign table
Note : You can eventually open this Foreign Table node and choose the label field.
By default (when you don't specify the Foreign Table label), the label field will be the default field of the table (Yellow star)

Is it clear enough ?

Source code : Have a look in prepareQuery(), you will find the function :

$this->filterPivot('travellers');

Then follow all the cascad of code to understand how it works.

The Filter accept multiple values, and you can customize the logic behaviors (AND, OR, NOT), but this is not in the builder.
To custom the logic, just set the desired logic in the state var.

if the relation is : 'travellers', the logic state var will be :

$this->setState('filter.travellers.logic', 'OR');'

To place BEFORE getItems() of the model.
It accepts : AND|OR|NOT

The LIST filter will be improved. It will accept normal FK as well in future.
The SELECT filter will be improved as well to recieve N:1 relations.

Now, only the Multiselect over N:m is available (was for you), it was the most complicated to achieve. The rest will come in future mondays.
I need to go now, so I will finish the docs and the monday feature later tonight.

Coding is now a piece of cake

The administrator has disabled public write access.

The following user(s) said Thank You: MorganL

Filters on pivots 28 Nov 2016 14:28 #14759

MorganL Offline Platinum Member Posts: 438 Thank you received: 53 Karma: 16	If this works, i may fly to whereever you are and give you a hug Trying now Morgan
	Morgan Leecy MCSE Novell / Linux PHP. MYSQL, Apache, node.js Coldfusion, JQuery, HTML5 Joomla The administrator has disabled public write access.

Filters on pivots 28 Nov 2016 16:44 #14760

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	Here it is ... www.j-cook.pro/index.php/p/pivot-filter All suggestions are welcome.
	Coding is now a piece of cake The administrator has disabled public write access. The following user(s) said Thank You: organicwebs

Filters on pivots 29 Nov 2016 02:31 #14761

organicwebs Offline Premium Member Chris Posts: 133 Thank you received: 21 Karma: 4	Works a treat - thankyou
	Just call me Chris The administrator has disabled public write access.

Filters on pivots 29 Nov 2016 08:52 #14763

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	MorganL wrote: If this works, i may fly to whereever you are and give you a hug Yeah... come to visit me... I live on a spanish Island in the mediterrean sea...
	Coding is now a piece of cake The administrator has disabled public write access. The following user(s) said Thank You: MorganL