By mrtoner on

I've got a block where I show a featured product; the product changes once a day. Here's how I'm selecting the product:

if (variable_get('featured_listing_last', 0) + (60 * 60 * 24) < time()) {
    $result = db_query('SELECT floor(rand() * count(*)) AS theOffset FROM {node} WHERE (type = "listing") AND (status = 1)');
    $theOffset = db_fetch_object($result);
    $result = db_query('SELECT * FROM {node} WHERE (type = "listing") AND (status = 1) LIMIT ' . $theOffset->theOffset . ', 1');
    $rec = db_fetch_object($result);
    $nodex = node_load(array("nid" => $rec->nid));
...

(This would be a perfect use for Views, but Views doesn't allow me to change a random sort on a schedule.)

Now I'd like to change the SELECT statement to only select those listings that are upgraded (field_listing_upgraded), but I don't know the correct syntax to accomplish this (a JOIN?). Can someone point me in the right direction?

(And if my code could be cleaner, please advise.)

⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

May be I am missing something but I think you can do that with views.

It supports random sort.

You can filter on field_listing_upgraded.

And I suspect you can manage the schedule part though the details of what that means are unclear. I am thinking a combination of node queue (in replace of field_listing_upgraded you make a queue representing upgraded items) plus Nodequeue Randomizer or Rules module (for the scheduling)

mrtoner’s picture

mrtoner commented

Yes, I realize that Views will do a random sort (and I can reduce the results to a single item), but it re-sorts the nodes every time the view is displayed.

I wasn't aware of Nodequeue Randomizer; it's exactly what I'm looking for if I use Nodequeue. However, the overhead involved in maintaining Nodequeue is way beyond what I'd like for this task. Besides adding four modules to my setup, I'd need to figure out how to add listings automatically to the queue when they're upgraded and remove them when they're de-listed.

A single select statement would be much simpler...

nevets’s picture

nevets commented

You can use the rules module to add/remove nodes to a queue when content is added/updated.

mrtoner’s picture

mrtoner commented

Thanks for the suggestion in that regard, but it's not as simple as that, since the content isn't being updated using standard means. And adding four modules instead of a single line of code is just overkill.

Really, all I need is the single SELECT statement that will return results based on CCK values.

a_lawry’s picture

a_lawry commented

Using views you can set the caching of the view. It's under basic settings in the view editor. Set it to "time based" then you can cache the output of the query or the rendered html output for 1 day.

beautifulmind’s picture

beautifulmind
he/him
Primary language Gujarati
Location Dubai
commented

By upgraded, did you mean updated?
Try this.

if (variable_get('featured_listing_last', 0) + (60 * 60 * 24) < time()) {
    $result = db_query('SELECT floor(rand() * count(*)) AS theOffset FROM {node} WHERE (type = "listing") AND (status = 1) AND changed = %d', strtotime('today'));
    $theOffset = db_fetch_object($result);
    $result = db_query('SELECT * FROM {node} WHERE (type = "listing") AND (status = 1) LIMIT ' . $theOffset->theOffset . ', 1');
    $rec = db_fetch_object($result);
    $nodex = node_load(array("nid" => $rec->nid));
...

Please note that I've altered the first query.

Regards.

Regards.
🪷 Beautifulmind

mrtoner’s picture

mrtoner commented

No, sorry, "upgraded." I have a CCK field, field_listing_upgraded, for this content type. I need to select the node based on that field.

naurisr’s picture

naurisr commented

One of these must work (if I understood your question):
1)

if (variable_get('featured_listing_last', 0) + (60 * 60 * 24) < time()) {
    $result = db_query('SELECT floor(rand() * count(*)) AS theOffset FROM {node} n INNER JOIN {content_listing_upgraded} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1)');
    $theOffset = db_fetch_object($result);
    $result = db_query('SELECT n.* FROM {node} n INNER JOIN {content_listing_upgraded} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) LIMIT ' . $theOffset->theOffset . ', 1');
    $rec = db_fetch_object($result);
    $nodex = node_load(array("nid" => $rec->nid));
...

2)

if (variable_get('featured_listing_last', 0) + (60 * 60 * 24) < time()) {
    $result = db_query('SELECT floor(rand() * count(*)) AS theOffset FROM {node} n INNER JOIN {content_type_listing} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded IS NOT NULL');
    $theOffset = db_fetch_object($result);
    $result = db_query('SELECT n.* FROM {node} n INNER JOIN {content_type_listing} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded IS NOT NULL LIMIT ' . $theOffset->theOffset . ', 1');
    $rec = db_fetch_object($result);
    $nodex = node_load(array("nid" => $rec->nid));
...
naurisr’s picture

naurisr commented

2)

if (variable_get('featured_listing_last', 0) + (60 * 60 * 24) < time()) {
    $result = db_query('SELECT floor(rand() * count(*)) AS theOffset FROM {node} n INNER JOIN {content_type_listing} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded IS NOT NULL');
    $theOffset = db_fetch_object($result);
    $result = db_query('SELECT n.* FROM {node} n INNER JOIN {content_type_listing} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded_value IS NOT NULL LIMIT ' . $theOffset->theOffset . ', 1');
    $rec = db_fetch_object($result);
    $nodex = node_load(array("nid" => $rec->nid));
...
mrtoner’s picture

mrtoner commented

#2 appears, from my untrained eye, to be the statement I need; however, I'm getting an error:

"user warning: Unknown column 'u.field_listing_upgraded' in 'where clause' query: SELECT floor(rand() * count(*)) AS theOffset FROM 7d_node n INNER JOIN 7d_content_type_listing u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded IS NOT NULL in ..."

naurisr’s picture

naurisr commented 
if (variable_get('featured_listing_last', 0) + (60 * 60 * 24) < time()) {
    $result = db_query('SELECT floor(rand() * count(*)) AS theOffset FROM {node} n INNER JOIN {content_type_listing} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded_value IS NOT NULL');
    $theOffset = db_fetch_object($result);
    $result = db_query('SELECT n.* FROM {node} n INNER JOIN {content_type_listing} u ON n.nid=u.nid WHERE (n.type = "listing") AND (n.status = 1) AND u.field_listing_upgraded_value IS NOT NULL LIMIT ' . $theOffset->theOffset . ', 1');
    $rec = db_fetch_object($result);
    $nodex = node_load(array("nid" => $rec->nid));
...
mrtoner’s picture

mrtoner commented

I should have been able to figure that out myself by just looking at the db. Thank you! I've looked at the MySQL docs several times and just couldn't make out how JOINs worked without a concrete example.