Date comparization in queries

[ChrisTERiS]

Hello,

 

I'm using the follow query in bundles table which in most cases works.

 

$ct_bundles = Capsule::table('tblbundles')
               ->whereDate('validfrom', '<=', date('Y-m-d'))
               ->whereDate('validuntil', '>=', date('Y-m-d'))
               ->orderBy('is_featured','desc')
               ->orderBy('sortorder','asc')
               ->get();

 

"Most cases" means that works when there is a value other than "0000-00-00". How I can modify it to ignore ->whereDate('validuntil', '>=', date('Y-m-d')) when value is 0000-00-00 ?

An idea that I've is to remove it at all and then work in the given array by removing all wrong array elements. But I think that this is a fast and dirty solution.

 

Thank you

Chris

Edited May 21, 2017 by ChrisTERiS

[ChrisTERiS]

For those who maybe need the solution is:

$ct_bundles = Capsule::table('tblbundles')
               ->whereDate('validfrom', '<=', date('Y-m-d'))
               ->whereDate('validuntil', '>=', date('Y-m-d'))
               ->orWhereDate('validuntil', '=', '0000-00-00')
               ->orderBy('is_featured','desc')
               ->orderBy('sortorder','asc')
               ->get();

 

I found it at: https://laravel.com/docs/5.4/queries and it works fine.

Edited May 21, 2017 by ChrisTERiS

[brian!]

i'd also suggest checking showgroup equals 1 - otherwise it's a hidden bundle and shouldn't be shown in the cart.

[ChrisTERiS]

i'd also suggest checking showgroup equals 1 - otherwise it's a hidden bundle and shouldn't be shown in the cart.

 

This is the first part of the query when there is no gid. The full query is:

   if (!$gid || $gid < 1)
   {
       $ct_bundles = Capsule::table('tblbundles')
               ->whereDate('validfrom', '<=', date('Y-m-d'))
               ->whereDate('validuntil', '>=', date('Y-m-d'))
               ->orWhereDate('validuntil', '=', '0000-00-00')
               ->orderBy('is_featured','desc')
               ->orderBy('sortorder','asc')
               ->get();
   } else {
       $ct_bundles = Capsule::table('tblbundles')
               ->where('gid',$gid)
               ->whereDate('validfrom', '<=', date('Y-m-d'))
               ->whereDate('validuntil', '>=', date('Y-m-d'))
               ->orWhereDate('validuntil', '=', '0000-00-00')
               ->orderBy('is_featured','desc')
               ->orderBy('sortorder','asc')
               ->get();        
   }

 

Do I still to add showgroup=1? Maybe the webmaster wants to have it hidden.

[brian!]

Do I still to add showgroup=1? Maybe the webmaster wants to have it hidden.

in my opinion, yes and you shouldn't need two different hooks depending on the value if $gid.

 

product bundles are by default hidden from the cart (e.g only accessible via direct URL) - only by ticking the "Show in Product Group" checkbox do they become visible in the cart.... from a database point of view, 'showgroup' is 0 by default, and '1' when the checkbox is ticked; also gid is 0 by default and contains the product group selected when showgroup checkbox is enabled.

 

however, let's say I enable SIPG and assign it to gid=3... therefore, showgroup = 1 & gid = 3; but if I later change my mind and untick the SIPG checkbox (to hide it from the cart), showgroup now equals 0, but gid still equals 3 (it isn't reset back to 0)... so your hook would end up showing hidden bundles.

[ChrisTERiS]

in my opinion, yes and you shouldn't need two different hooks depending on the value if $gid.

 

product bundles are by default hidden from the cart (e.g only accessible via direct URL) - only by ticking the "Show in Product Group" checkbox do they become visible in the cart.... from a database point of view, 'showgroup' is 0 by default, and '1' when the checkbox is ticked; also gid is 0 by default and contains the product group selected when showgroup checkbox is enabled.

 

however, let's say I enable SIPG and assign it to gid=3... therefore, showgroup = 1 & gid = 3; but if I later change my mind and untick the SIPG checkbox (to hide it from the cart), showgroup now equals 0, but gid still equals 3 (it isn't reset back to 0)... so your hook would end up showing hidden bundles.

 

Got it, thank you ! But I think that is good to keep gid to show bundles in the right group listing.

 

I'm going fasten than I was expecting. Maybe tomorrow night should be ready

https://www.teriakis.com/cart.php?gid=1

 

This is normal cart. Clicking on the "Product details" you're redirecting to product page (not cart). Clicking Buy now you're going back to configuration cart page.