http://qs321.pair.com?node_id=1207842

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

[download]
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();

[download]

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
[reply]

      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)

[reply]
[d/l]
[select]

        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.

[reply]
      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

      [download]
      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).
[reply]
[d/l]
[select]
        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');

        [download]
        poj
[reply]
[d/l]
      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?
[reply]
[d/l]
        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.
[reply]

Back to Seekers of Perl Wisdom