Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Perl DBI not storing @prev_value in query

by Beaker (Beadle)
on Jan 24, 2018 at 16:22 UTC ( #1207842=perlquestion: print w/replies, xml ) Need Help??

Beaker has asked for the wisdom of the Perl Monks concerning the following question:

I have a query to return the most recent n posts per discussion (example below has n as 3).
SELECT x.group_discussion_id, x.comment, x.date_posted, x.username, x.realname, TO_DAYS(NOW()) - TO_DAYS(x.date_posted) AS date_posted_days FROM ( SELECT gdp.group_discussion_post_id, gdp.group_discussion_id, gdp.comment, gdp.date_posted, m.username, m.realname, @recent_post_rank := IF ( @prev_discussion = gdp.group_discussion_id, @recent_post_rank + 1, 1 ) AS recent_post_rank, @prev_discussion := gdp.group_discussion_id FROM group_discussion_posts gdp JOIN members m ON m.memberid = gdp.memberid WHERE gdp.active = 1 AND gdp.reply_to_post_id != 0 AND gdp.group_discussion_id IN (1, 2, 3) ORDER BY gdp.group_discussion_id DESC, gdp.group_discussion_post_id DESC ) x WHERE recent_post_rank <= 3
This query works perfectly in MySQL, I have also run the $sth->{Statement}; output which runs the same. However, running the query on my site, using DBI does not return the same output, it does not restrict the rows returned to the n value i.e. all of the matching group_discussion_posts are returned. Debugging this I found that dbi returns the recent_post_rank as 1 for every row (unlike when it is run directly in MySQL), and there is no value for @prev_discussion Therefore, it would appear that DBI is not properly handling this @prev_discussion stored value. Any ideas?
my $sth = $dbh->prepare('SELECT x.group_discussion_id, x.comment, +x.date_posted, x.username, x.realname, TO_DAYS(NOW()) - TO_DAYS(x.date_posted) AS date_posted_days FROM ( SELECT gdp.group_discussion_post_id, gdp.group_discussion_id, gdp.comment, gdp.date_posted, m.username, m.realname, @recent_post_rank := IF ( @prev_discussion = gdp.group_discussion_id, @recent_post_rank + 1, 1 ) AS recent_post_rank, @prev_discussion := gdp.group_discussion_id FROM group_discussion_posts gdp JOIN members m ON m.memberid = gdp.memberid WHERE gdp.active = 1 AND gdp.reply_to_post_id != 0 AND gdp.group_discussion_id IN (1, 2, 3) ORDER BY gdp.group_discussion_id DESC, gdp.group_discussion_post_id DESC ) x WHERE recent_post_rank <= 3' ); $sth->execute(); return $sth->fetchall_arrayref();

Replies are listed 'Best First'.
Re: Perl DBI not storing @prev_value in query
by Mr. Muskrat (Canon) on Jan 24, 2018 at 17:03 UTC

      A few months back, I was able to get some proof of concept code working. See my thread, especially the summary code in this post. I don't know whether the things I learned in that set of experiments will help, but I was able to make use of the @dmvar user-defined variable; I don't think it had to be inside of the DebugMe mySQL function to work... that's just specifically what I was trying to get at for that one. But I thus know that user-defined variables aren't out of the question in DBD::mysql.

      (caveat: not an SQL or mySQL expert)

        I haven't used MySQL for many years now (not since I left Lacuna Expanse any way) so I'm by no means an expert either.

        I just don't know enough about MySQL's user-defined variables. I would totally expect them to work inside of stored functions. If they work as a part of normal queries as well then it would useful if the DBD::mysql documentation at least mentioned them.

      hurray I found a solution, by adding : , (SELECT @prev_discussion:=0) pd, (SELECT @recent_post_rank:=0) rpr as follows:
      SELECT x.group_discussion_id, x.comment, x.date_posted, x.username, x.realname, TO_DAYS(NOW()) - TO_DAYS(x.date_posted) AS date_posted_days FROM ( SELECT gdp.group_discussion_post_id, gdp.group_discussion_id, gdp.comment, gdp.date_posted, m.username, m.realname, @recent_post_rank := IF ( @prev_discussion = gdp.group_discussion_id, @recent_post_rank + 1, 1 ) AS recent_post_rank, @prev_discussion := gdp.group_discussion_id FROM group_discussion_posts gdp JOIN members m ON m.memberid = gdp.memberid, (SELECT @prev_discussion:=0) pd, (SELECT @recent_post_rank:=0) rpr WHERE gdp.active = 1 AND gdp.reply_to_post_id != 0 AND gdp.group_discussion_id IN (1, 2, 3) ORDER BY gdp.group_discussion_id DESC, gdp.group_discussion_post_id DESC ) x WHERE recent_post_rank <= 3
      Hopefully this might help someone else in the future. I'm not sure exactly why it works, but it creates a subquery to select each user defined variable, and an alias for each (which is never used).
        I'm not sure exactly why it works

        It works because the user defined variables need to be initialized. Because variables are session specific they are 'retained' when using MySQL workbench. You should notice that your original query does not work in MySQL workbench on the first run but then works on subsequent runs. Using DBD you are creating a new session each time so variables never get initialized. You could use your original query by adding a set command before the execute

        $dbh->do('SET @recent_post_rank := 0, @prev_discussion := 0');
        poj
      I have a statement including this: @row_num := @row_num + 1 AS rownum elsewhere in my code (in a $dbh->selectrow_array) and it works as expected, so it seems to be that it allows variables but doesn't not "store" them. Strange, as I thought DBI just sends the query to MySQL, I didn't think it needed to understand every aspect of the query?
        This is tricky because I have refactored with MySQL query as my last version was very inefficient, taking 5 seconds + to execute. This one is super fast but it doesn't look like I can use it if DBI won't support it, so I'm stuck now.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1207842]
Approved by stevieb
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (5)
As of 2022-06-27 06:30 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My most frequent journeys are powered by:









    Results (86 votes). Check out past polls.

    Notices?