Databases
StackOverflow2013
Postsdbo
POSELECT where ID matches one of a list
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSELECT where ID matches one of a list
    text
    copied!<p>I'm just learning the ins and outs of MYSQL queries but I've run into a roadblock with a project and I'd appreciate some help reaching a solution.</p> <p>I have two tables, the first contains a reference to the entry (entry_id) and the modification associated (mod_id) as well as the category of modification it refers to (mod_name)</p> <p>Table 1: exp_store_product_options</p> <pre><code>mod_id entry_id mod_name 3 2919 COLOR 4 2311 SIZE 5 2311 COLOR 6 3301 COLOR </code></pre> <p>the second table contains two relevant fields, mod_id and opt_name</p> <p>Table 2: exp_store_product_modifiers</p> <pre><code>mod_id opt_name 3 BLACK 4 EU 44 5 BROWN 6 BROWN </code></pre> <p>What I am trying to achieve, is a listing of all the DISTINCT opt_name fields that (through a join on the mod_id) correspond to entry_ids that I would like to pass in as a lump.</p> <p>here is the code I've come up with so far, I believe it'll do what I need aside from requiring me to loop through the query for each entry id, and failing on the DISTINCT requirement since for each iteration, everything is distinct. (the {sale_products} tags are from ExpressionEngine, and will loop during the parse to provide me with a list of the entry_id results that are relevant to this page</p> <pre><code>SELECT DISTINCT opt_name FROM exp_store_product_options INNER JOIN exp_store_product_modifiers ON exp_store_product_options.product_mod_id=exp_store_product_modifiers.product_mod_id {sale_products parse='inward'}entry_id = '{entry_id}' OR{/sale_products} AND mod_name = 'SIZE' </code></pre> <p>====================================================</p> <h1>POSTMORTEM</h1> <p>Just in case anyone else is trying to work within expresso's Store module for ExpressionEngine and needs to build some rudimentary filtering into your templates, here's the code I ultimately got to work. Very similar to Ben's code, but utilizing embeds instead of directly inserting the entry_ids because of issues with parse order:</p> <p>the template: embeds/product_filter</p> <pre><code>{exp:query sql="SELECT DISTINCT opt_name FROM exp_store_product_modifiers LEFT JOIN exp_store_product_options ON exp_store_product_options.product_mod_id = exp_store_product_modifiers.product_mod_id WHERE exp_store_product_modifiers.entry_id IN ({embed:entry_ids}) AND exp_store_product_modifiers.mod_name = '{embed:filter_on}'" } &lt;li&gt;&lt;a href="#"&gt;{opt_name}&lt;/a&gt;&lt;/li&gt; {/exp:query} </code></pre> <p>with an {embed} that looks like</p> <pre><code>{embed="embeds/product_filter" entry_ids="{sale_products backspace='1'}{entry_id},{/sale_products}" filter_on="SIZE"} </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload