Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: [ADDED] Filters on pivots

Filters on pivots 08 Aug 2016 18:37 #14387

  • MorganL
  • MorganL's Avatar
  • 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
  • MorganL's Avatar
  • 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
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • organicwebs's Avatar
  • Online
  • 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
  • organicwebs's Avatar
  • Online
  • 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
  • organicwebs's Avatar
  • Online
  • 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
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • organicwebs's Avatar
  • Online
  • 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 :woohoo:
Just call me Chris :)
The administrator has disabled public write access.

Filters on pivots 28 Nov 2016 11:14 #14754

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • MorganL's Avatar
  • 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
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • organicwebs's Avatar
  • Online
  • 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
  • admin's Avatar
  • Offline
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
  • 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
  • Page:
  • 1
  • 2
Time to create page: 0.128 seconds

Get Started