Convert sql code to work on VanillaModel

VanillaFan

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 ?

businessdad

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

x00

How about:

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

businessdad

@x00 said:
How about:

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

That would definitely be better.

x00

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

VanillaFan

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

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)

VanillaFan

@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;`

businessdad

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

VanillaFan

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

VanillaFan

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

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!

peregrine

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)

VanillaFan

@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

peregrine

the pleasure is mine.