Blog Documentation Try Vanilla Cloud
Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Convert sql code to work on VanillaModel

VanillaFanVanillaFan ✭
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()')

    My shop | About Me

  • businessdadbusinessdad Stealth contributor MVP

    @x00 said:
    How about:

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

    That would definitely be better.

    My shop | About Me

  • x00x00 MVP
    edited August 2014

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

    grep is your friend.

  • VanillaFanVanillaFan ✭

    @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 Ex-Fanboy Munich Admin

    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)

  • VanillaFanVanillaFan ✭

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

    My shop | About Me

  • VanillaFanVanillaFan ✭
    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?

  • VanillaFanVanillaFan ✭

    @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 Ex-Fanboy Munich Admin

    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! :)

  • peregrineperegrine MVP

    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.

  • VanillaFanVanillaFan ✭

    @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:

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

Sign In or Register to comment.