Query if string contains any value from array

#1

Hi,
Getting to the head bashing stage with this one so hoping for someone to ‘bring the light’

I’m trying to set up some toggleable filters, so the user can select multiple fields from a multi select and toggle the filter to be AND/OR.

The query is a paged query, pulling data from a view defined as:

SELECT
	list_status_type.id, 
	list_status_type.status_type, 
	list_status_type.active, 
	list_status_type.updated_on, 
	list_status_type.universal, 
	CONCAT (user_profile.first_name, " ",user_profile.surname) AS 'name',
	GROUP_CONCAT(DISTINCT community.community SEPARATOR ',') AS 'Community',
	GROUP_CONCAT(DISTINCT list_status.status SEPARATOR ',') AS 'Status', 
	GROUP_CONCAT(DISTINCT list_status.id SEPARATOR ',') AS 'Status_id',
	GROUP_CONCAT(DISTINCT community_status_type.community_id ) AS 'Community_id'
FROM
	list_status_type
	LEFT JOIN
	list_status
	ON 
		list_status_type.id = list_status.status_type_id
	LEFT JOIN
	community_status_type
	ON 
		list_status_type.id = community_status_type.status_type_id
	LEFT JOIN
	community
	ON 
		community.id = community_status_type.community_id
	LEFT JOIN
	user_profile
	ON 
		list_status_type.updated_by = user_profile.id
GROUP BY
	list_status_type.id

There is a condition in the query (I’ve not set the AND/OR toggle yet):

image
image791×140 6.79 KB

This works fine for the AND filter, so if ALL of the selected communities are in the column it returns it. I can’t get it to work for the OR filter, so if ANY of the selected communities are in the community_id column they return.

I’d prefer to keep the view as it is if possible, I’ve tried with CONTAINS, and IN and by splitting, flattening, and returning values from the $_GET.filtercommunities. They all seem to return the values if all the id’s are contained.

Is there anything I’m missing?

#2

Hi,
Looking for the same answer?
Have you found how to do it?
Thanks

#3

@sylvainbaron, Here is how I approach this kind of question…

Which is that I’ve come to realise the limitations of using a graphical interface for super complex queries, and I’ve moved to writing my own MySQL views for them instead.

It takes a short while to get your head around them, and you need to do the work outside of Wappler, but they are super powerful, and once written can be used and re-used in Wappler database queries in different parts of your app.

Just a thought if you’re banging your head against a brick Wappler Wall… I found it better to invest my time in learning the oh-so-powerful SQL equivalent!

#4

Thanks for your answer.
I found a solution using {{query1.groupBy('id').keys()}}
:smiley: