I’m using postgres >= 9.6. I have tasks, tags and task_tags tables, for a typical n:m relationship between tasks and tags.
I’d like to be able to programmatically build queries against the tasks table that supports queries against the actual fields of tasks, but also on the tags (tag names) of a task.
Queries on the task fields themselves are straight-forward. Queries on the tags for a “does it have tag A?” are also straight-forward. What I am struggling with is coming up with a select/query structure that would allow me to also match things like “does it have tag A and tag B”?
The best I’ve come up with is a lateral join on a subquery with an array aggregation, and then using array matching functions, e.g.:
SELECT DISTINCT ON (tasks.id) tasks.* FROM tasks, LATERAL ( SELECT array_agg(tags.name) AS tags FROM task_tags INNER JOIN tags ON task_tags.tag_id = tags.id WHERE task_tags.task_id = tasks.id GROUP BY task_tags.task_id) tt WHERE tt.tags @> array['tag1'::varchar, 'tag3'::varchar];
That way, it should be possible to programmatically build a WHERE clause (using tasks.* and tt.tags) satisfying all of the conditions of the user-provided “query”.
However, I’m not sure if this is the best way of doing it – thoughts? Is this query any efficient? Is there any index I could create that would improve it?
Similarly, is there any way at all of making it work with wildcards against the tag names? Normal array matching wouldn’t allow that, and solutions I’ve seen suggest using unnest (or, well, not using arrays in the first place), but then I’d lose the ability of saying “it needs to have both tagA and tagB”.
Is there any other way of building a query on these relationships that would allow that kind of “both tagA and tagB” matching?
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!