Skip to content

Instantly share code, notes, and snippets.

@oak-tree

oak-tree/term filter

Created April 15, 2015 08:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save oak-tree/04d1ba22547d9faaeb08 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save oak-tree/04d1ba22547d9faaeb08 to your computer and use it in GitHub Desktop.
Download ZIP
mysql key,value query with multiply keys
Raw
term filter
SELECT term_id,count(name) as counter FROM `tbl_term_character`
WHERE
(name="Size" && (value & b'10'))
||
(name="HeadColors" && (value & b'110'))
group by term_id
having counter = 2
@oak-tree
Copy link
Author

oak-tree commented Apr 15, 2015

combine it with the tbl_term

two options

IN

  1. select id,hebrewName from tbl_term where id in (SELECT term_id FROM tbl_term_character WHERE (name="Size" && (value & b'100')) || (name="HeadColors" && (value & b'110')) group by term_id having count(name) = 2) ORDER BY tbl_term.urlBestBigPhoto ASC

Join

select id,hebrewName from tbl_term as t1 join (SELECT term_id FROM tbl_term_character
WHERE

(name="Size" && (value & -1))

group by term_id
having count(name) = 1) as t2
ON t1.id = t2.term_id

@oak-tree
Copy link
Author

oak-tree commented Apr 15, 2015

with the name as well
select id,hebrewName from tbl_term as t1 join (SELECT term_id FROM tbl_term_character
WHERE
(name="Size" && (value & -1))
group by term_id
having count(name) = 1) as t2

ON t1.id = t2.term_id
where t1.hebrewName <> ""

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment