Please upgrade here. These earlier versions are no longer being updated and have security issues.

Convert sql code to work on VanillaModel

This discussion is related to the Thankful People 2 addon.
edited August 2014 in Vanilla 2.0 - 2.8

I"m try to develop new plugin that display top five thanks received uses on last 7 days..here working code that give me expect result

   SELECT COUNT(vf_ThanksLog.DateInserted) AS Thanks,vf_ThanksLog.UserID,vf_User.Name
    FROM vf_ThanksLog
    LEFT JOIN vf_User
    ON vf_ThanksLog.UserID=vf_User.UserID
    WHERE vf_ThanksLog.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW()
    GROUP BY `UserID`
    ORDER BY thanks DESC
    LIMIT 05;

Now i want convert it work on VanillaModel..here my code

        SQL
        ->Select('u.UserID, u.Name,u.Email, u.Photo')
        ->Select( 'tc.DateInserted','count','ThankCount')
        ->From('ThanksLog tc')
        ->Join('User u', 'tc.UserID = u.UserID')
        ->WHERE('tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW()') 
        ->GroupBy('u.UserID')
        ->Limit("$limit")
        ->OrderBy('ThankCount', "Desc")  
        ->Get()
        ->ResultArray();

It's given me some result,but thier where code not working as expect

when debug

 select u.UserID as `UserID`, u.Name as `Name`, u.Email as `Email`, u.Photo as `Photo`, count(tc.DateInserted) as `ThankCount`
from vf_ThanksLog tc
join vf_User u on tc.UserID = u.UserID
where tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW() is null
group by u.UserID
order by ThankCount desc
limit 5;

so problem is how to remove is null on where code

where tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW() is null

please help me..any solution ?

Tagged:

Comments

  • businessdadbusinessdad Stealth contributor MVP
    edited August 2014

    The Where() method takes two arguments:

    • A field, plus an optional comparison operator: SomeField, SomeField =
    • A value to which the field will be compared.

    If you leave the second argument empty, you are saying "compare field tc.DateInserted BETWEEN NOW()-INTERVAL 6 DAY AND NOW() with null".

    I'm not 100% sure it will work, but have you tried this?

    ->Where('tc.DateInserted BETWEEN' , 'NOW()-INTERVAL 6 DAY AND NOW()')
    
    vrijvlinderUnderDog
  • businessdadbusinessdad Stealth contributor MVP

    @x00 said:
    How about:

    ->Where('tc.DateInserted >', 'NOW()-INTERVAL 6 DAY')
    ->Where('tc.DateInserted <', 'NOW()')
    

    That would definitely be better.

    UnderDog
  • x00x00 MVP
    edited August 2014

    you're the sql nut, is there any difference at in performance?

    grep is your friend.

  • @businessdad said:

    I'm not 100% sure it will work, but have you tried this?

    ~~~

    ->Where('tc.DateInserted BETWEEN' , 'NOW()-INTERVAL 6 DAY AND NOW()')
    ~~~

    when i used it, it's given me vanilla Bonk error page :'(

    Thanks for help friend ,those additional details help me to improve knowledge

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    That's the head of the Where function:

       /**
        * Adds to the $this->_Wheres collection. Called by $this->Where() and $this->OrWhere();
        *
        * @param mixed $Field The string on the left side of the comparison, or an associative array of
        * Field => Value items to compare.
        * @param mixed $Value The string on the right side of the comparison. You can optionally
        * provide an array of DatabaseFunction => Value, which will be converted to
        * DatabaseFunction('Value'). If DatabaseFunction contains a '%s' then sprintf will be used for to place DatabaseFunction into the value.
        * @param boolean $EscapeFieldSql A boolean value indicating if $this->EscapeSql method should be called
        * on $Field.
        * @param boolean $EscapeValueString A boolean value indicating if $this->EscapeString method should be called
        * on $Value.
        */
       public function Where($Field, $Value = NULL, $EscapeFieldSql = TRUE, $EscapeValueSql = TRUE) {
    

    If it is not already working with the solution businessdad‌ has provided, you can try where('tc.DateInserted', 'BETWEEN NOW()-INTERVAL 6 DAY AND NOW()', true, false)


  • @x00 said:
    you're the sql nut, is there any difference at in performance?

    1 queries executed, 1 success, 0 errors, 0 warnings

    0 row(s) returned

    :'(

    Debug result

    select u.UserID as `UserID`, u.Name as `Name`, u.Email as `Email`, u.Photo as `Photo`, count(tc.DateInserted) as ThankCount`
    from vf_ThanksLog tc
    join vf_User u on tc.UserID = u.UserID
    where tc.DateInserted > 'NOW()-INTERVAL 6 DAY'
     and tc.DateInserted < 'NOW()'
    group by u.UserID
    order by ThankCount desc
    limit 5;`
    
  • businessdadbusinessdad Stealth contributor MVP
    edited August 2014

    @VanillaFan said:
    when i used it, it's given me vanilla Bonk error page :'(

    As I wrote, I wasn't sure it would work (support for the BETWEEN clause is still a bit dodgy in Vanilla framework). You can use the example given by @x00, which should work.

    @x00 said:
    you're the sql nut, is there any difference at in performance?

    Universal DBA answer: it depends. :D
    Practical answer: in most RDBMS, the BETWEEN clause is transformed in two AND conditions by the engine, it just serves as a handy shortcut. I'm not aware of significant differences in query plans when using BETWEEN or two separate WHERE clauses.

    Ironically, when dealing with dates, the BETWEEN is often useless, as a it includes the specified values, while, with dates, you usually want to exclude the last one.

    That is:

    • WHERE MyField BETWEEN '20140101' AND '20140201' will retrieve the data from the 1st of January to 2nd of February included.
    • WHERE (MyField >= '20140101') AND (MyField < '20140201') will retrieve the data from the 1st of January included to the 31st of January (i.e. the 2nd of February is excluded, as it should be).

    @x00 I'm sure you are aware of that, I just added it for anyone who might not be aware of the difference.

    peregrinevrijvlinderUnderDog
  • edited August 2014

    @x00 said:
    How about:

    ->Where('tc.DateInserted >', 'NOW()-INTERVAL 6 DAY')
    ->Where('tc.DateInserted <', 'NOW()')
    

    debug code

    WHERE tc.DateInserted > 'NOW()-INTERVAL 6 DAY' AND tc.DateInserted < 'NOW()'

    0 row(s) returned :'(

    test that code directly phpmyadmin after modify as below it's given me correct result :(

    WHERE tc.DateInserted > NOW()-INTERVAL 6 DAY AND tc.DateInserted < NOW()

    any help?

  • @R_J said:
    If it is not already working with the solution businessdad‌ has provided, you can try where('tc.DateInserted', 'BETWEEN NOW()-INTERVAL 6 DAY AND NOW()', true, false)

    when i used it, it's given me vanilla Bonk error page :'(

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    Yes, I've tried the following (had to change the table though):

    $Sql = Gdn::SQL();
    $Select = $Sql
        ->Select('u.UserID, u.Name,u.Email, u.Photo')
        ->Select( 'tc.DateInserted','count','ThankCount')
        ->From('Comment tc')
        ->Join('User u', 'tc.UserID = u.UserID')
        ->WHERE('tc.DateInserted', 'BETWEEN NOW()-INTERVAL 6 DAY AND NOW()', true, false)
        ->GroupBy('u.UserID')
        ->Limit("$limit")
        ->OrderBy('ThankCount', "Desc")
        ->GetSelect();
    
        decho($Select);
    

    and got

    select u.UserID as `UserID`, u.Name as `Name`, u.Email as `Email`, u.Photo as `Photo`, count(tc.DateInserted) as `ThankCount`
    from V21_Comment tc, V21_User `User` join V21_User u on tc.UserID = u.UserID
    where tc.DateInserted = BETWEEN NOW()-INTERVAL 6 DAY AND NOW()
     and UserID = :UserID
     group by u.UserID
     order by ThankCount desc
    

    which is really strange...

    But at least the where clause isn't quoted any more! :)


  • see if this helps.

      try this set the variables first.
                                 // more than  this date
                                $minago = Gdn_Format::ToDateTime(strtotime('-3 days'));
                                //  less than this date
                                $maxago = Gdn_Format::ToDateTime(strtotime('-1 days'));
    
    
        then for the where's try this.....
    
                        ->Where('tc.DateInserted >',$minago)                                
                                     ->Where('tc.DateInserted <',$maxago)
    

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

    R_JvrijvlinderUnderDogVanillaFan
  • @peregrine said:
    see if this helps.

      try this set the variables first.
                                 // more than  this date
                                $minago = Gdn_Format::ToDateTime(strtotime('-3 days'));
                                //  less than this date
                                $maxago = Gdn_Format::ToDateTime(strtotime('-1 days'));
                  
        
        then for the where's try this.....
        
                      ->Where('tc.DateInserted >',$minago)                                
                                     ->Where('tc.DateInserted <',$maxago)
    

    finally at last it's work..thank u very much :smile:

    peregrinevrijvlinderUnderDog
  • peregrineperegrine MVP
    edited August 2014

    the pleasure is mine. :)

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

    vrijvlinderUnderDog
Sign In or Register to comment.