StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can probably (tested on MySQL 5.5 without bound parameters) use your conditions in the <code>SELECT</code> clause, they should come out 0 (failed) or 1 (passed):</p> <pre><code>SELECT message_id, message_content, (time_before_reminder < :days_since_visit) AS time, (message_id NOT IN (SELECT message_id FROM sentemails WHERE user = :userid)) AS already, (:userid NOT IN (SELECT userid FROM sentemails WHERE date_add(timesent, interval 3 day) >= now())) AS recent FROM emailtemplates WHERE time_before_reminder < :days_since_visit AND message_id not in (SELECT message_id FROM sentemails WHERE user = :userid) AND :userid not in (SELECT userid FROM sentemails WHERE date_add(timesent, interval 3 day) >= now()) ORDER BY time_before_reminder asc LIMIT 1; </code></pre> <p>However, I suppose this means twice the work for the DBMS. It must also be possible to use some <code>LEFT JOIN</code> and see what fields are null... and that'd be more elegant, and probably faster too.</p> <p>Another thing - though irrelevant for your question - what's the point having an <code>ORDER BY</code> followed by a <code>LIMIT 1</code> ?</p> <p><strong>Edit</strong>: the <code>LEFT JOIN</code>-based request should be something like this (tell me if it works, I can't test it unless I create the DB... and I feel lazy ^^):</p> <pre><code>SELECT et.message_id AS messageId, et.message_content AS messageContent, se1.message_id AS nullIfNotSent, se2.userid AS nullIfMoreThan3Days FROM (SELECT :userid AS userid) AS param LEFT JOIN sentemails AS se2 ON param.userid=se2.userid AND date_add(se2.timesent, interval 3 days) >= now(), emailtemplates AS et LEFT JOIN sentemails AS se1 ON et.message_id=se1.message_id AND se1.userid=param.userid WHERE time_before_reminder < :days_since_visit ORDER BY time_before_reminder LIMIT 1; </code></pre> <p><em>Expected behavior</em>: zero rows returned means it failed the first test (<code>time_before_reminder</code>), otherwise if <code>nullIfNotSent</code> is not null (therefore a valid <code>message_id</code>) it means it failed the second test (this kind of message has already been sent someday), and if <code>nullIfMoreThan3days</code> is not null (a valid user ID) it means the user already received a message in the past 3 days (i.e. failed the third test). So if you get a row with <code>nullIfNotSent</code> and <code>nullIfMoreThan3Days</code>, it means you can send a message using the returned <code>messageId</code> and <code>messageContent</code>.</p> <p>Please tell if it does work! (or doesn't) ;)</p>

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