Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product
Please upgrade to 2.3 here. The 2.2 and earlier branches are no longer being updated.

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.3

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 ?

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.