How to render MIGXDB results by filtering for one word in a column?

115 Posts

jimmyjazz Reply #1, 6 years, 11 months ago

I'm trying to render MIGXDB results by filtering for one word in a column, in this case the word is actually a number (1800).

What I have is thousands of dates in a column called 'birthday' but I only want to display the results of the birthdays in 1800.

birthday examples:

21st May 1800
22nd June 1801
4th July 1887
1st April 1984
30th December 1925
9th March 1800

So far I tried various codes to try to display the results from 1800 only but all not worked yet:

1st attempt (rendered no results)

[[!migxLoopCollection?
&packageName=`datequote`
&classname=`DateQuote`
&selectfields=`id,quote,author,birthday`
&where=`{"birthday":"1800"}`
&tpl=`myTplTest`
&sortConfig=`[{"sortby":"pos"}]`
]]

2nd attempt (i tried to add in a 'if' regular expression but this just results in the page loading the entire collection)

[[!migxLoopCollection?
&packageName=`datequote`
&classname=`DateQuote`
&selectfields=`id,quote,author,topic,birthday`
&where=`{"birthday":"if(preg_match_all("/find'(.+?)'/", $1800, $matches)) {
    array_shift($matches);
    print_r($matches);
}
else {
    //no matches
}"}`
&tpl=`myTplTest`
&sortConfig=`[{"sortby":"pos"}]`
]]

Any ideas how to achieve this?
I'm kinda hoping there's a way to do this without adding an extra column because it would take a long time to update thousands of fields with the year only.

This question has been answered by Bruno17. See the first response.

932 Posts

LK Reply #2, 6 years, 11 months ago

I had a similar requirement last week but ultimately decided that searching a huge amount of data in a template variable, especially a JSON string wasn't ideal.

What I ended up doing was grabbing all of the JSON code for all resources (along with their ID) with that TV and then simply using sublime text to find and delete all instances of the JSON leaving just the numeric or keyword values. Then I replaced the comma delimination with double pipes || and created a new multi-select TV. I then used ImportX (set to update) to apply the tv and relevant tags to the respective resource.

From there you can just use getResources to filter.

Not sure if that's a potential solution for you but it's how I went about tackling the issue.

■ email: [email protected] | ■ website: https://alienbuild.uk

The greatest compliment you can give back to us, is to spend a few seconds leaving a rating at our trustpilot: https://uk.trustpilot.com/review/alienbuild.uk about the service we provided. We always drop mention of services offered by businesses we've worked with in the past to those of interest.

discuss.answer

5,888 Posts

Bruno17 Reply #3, 6 years, 11 months ago

@LK, he es talking about MIGXdb, not MIGX

usually, you would store dates as datetime
Then you could do:

&where=`{"YEAR(bithday)":"1800"}`

but in your case, you can try:

&where=`{"birthday:LIKE":"%1800%"}`

which will be much slower, than having proper indexed datetime - fields and using the YEAR - sql-function

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

115 Posts

jimmyjazz Reply #4, 6 years, 11 months ago

Quote from: Bruno17 at May 19, 2017, 04:48 PM

@LK, he es talking about MIGXdb, not MIGX

usually, you would store dates as datetime
Then you could do:
&where=`{"YEAR(bithday)":"1800"}`
but in your case, you can try:
&where=`{"birthday:LIKE":"%1800%"}`
which will be much slower, than having proper indexed datetime - fields and using the YEAR - sql-function

Hi Bruno, yes that worked just fine, only entries from 1800 are showing now

Yes you are correct I should have had a field for YEAR. I started this a long time ago and as it progressed other ideas were coming to me, when I came up with this idea about showing for every year it was around 18,000 entries which made it too daunting a prospect to add the extra field. It's over 23,000 entries now.

So thank you so much, your advice has been so helpful.

115 Posts

jimmyjazz Reply #5, 6 years, 11 months ago

Just one further question if you don't mind.....

About the sort order, currently I have this:

&sortConfig=`[{"sortby":"pos"}]`

And it is starting in September and the months are mixed up.

I tried deleting this line but it still does not show chronologically by each month (January to December)

I have one field called 'month' which is not used in this code, here it is just the month name that is the input data.

Is it possible to use the month name to sort the order so the list goes from Jan to Dec?
Or is there another method?

5,888 Posts

Bruno17 Reply #6, 6 years, 11 months ago

I really would recommend storing dates in datetime - fields. Then filtering and sorting by dates will get a lot easier and faster!

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

Answered How to render MIGXDB results by filtering for one word in a column?