OK, I don't get it!!!
The SQL code generated looks OK, but I don't get the results...!
I am sure there is (are) better ways, I am still discovering cake (and
PHP, BTW)!
Here is the request generated (got it through debug):
SELECT "Stock"."product_id" AS "Stock__product_id",
"Stock"."location_id" AS "Stock__location_id", "Stock"."on_hand_units"
AS "Stock__on_hand_units", "Stock"."committed_units" AS
"Stock__committed_units", "Stock"."on_order_units" AS
"Stock__on_order_units", "Stock"."on_hand_dollars" AS
"Stock__on_hand_dollars", "Stock"."exch_cost" AS "Stock__exch_cost",
"Stock"."last_exch_cost" AS "Stock__last_exch_cost", "Stock"."created"
AS "Stock__created", "Stock"."modified" AS "Stock__modified",
"Product"."id" AS "Product__id", "Product"."description" AS
"Product__description", "Product"."size_description" AS
"Product__size_description", "Product"."section_size" AS
"Product__section_size", "Product"."aspect_size" AS
"Product__aspect_size", "Product"."tire_rim_size" AS
"Product__tire_rim_size", "Product"."plg_type" AS "Product__plg_type",
"Product"."speed_rating" AS "Product__speed_rating",
"Product"."load_rating" AS "Product__load_rating", "Product"."side_wall"
AS "Product__side_wall", "Product"."created" AS "Product__created",
"Product"."modified" AS "Product__modified", "Location"."id" AS
"Location__id", "Location"."name" AS "Location__name",
"Location"."opensunday" AS "Location__opensunday", "Location"."created"
AS "Location__created", "Location"."modified" AS "Location__modified"
FROM "stocks" AS "Stock" LEFT JOIN "products" AS "Product" ON
"Stock"."product_id" = "Product"."id" LEFT JOIN "locations" AS
"Location" ON "Stock"."location_id" = "Location"."id" WHERE
"Product"."section_size" = 265 AND "Product"."aspect_size" = 70 LIMIT 100
the same (simplified) request in Postgresql:
SELECT * FROM stocks LEFT JOIN products ON stocks.product_id
=products.id LEFT JOIN locations ON stocks.location_id = locations.id
WHERE products.section_size = 265 AND products.aspect_size = 70
give me 58 (expected) rows, while, if I have one of the values, like
section_size, where there is no match, I get no results with cake
(expected), but I get ALL rows (422) if there is a match!
here is the code in my controller, which, I am sure, will make some
laugh....
Thanks for any help!
class StocksController extends AppController
{
var $name = 'Stocks';
var $components = array('Filter');
var $helpers = array('Html','Javascript','Time','Filter');
var $uses = array('Stock', 'Product') ;
function index($page = '1', $location = '0')
{
$this->checkSession();
uses('sanitize');
$cleaner = new Sanitize();
$page = $cleaner->paranoid($page);
if(!empty($this->data))
{
$temparray = ($this->cleanArray($this ->
data,'Product')) ;
$myquery =
$this->buildRequest($temparray['Product']);
if (count($temparray['Product']) <> 0)
{
print_r($myquery);
$this->set('Stocks',
$this->Stock->findAll($myquery,"","",100, $page));
$this->set('page',$page);
$this->set('total_stocks',$this->Stock->findCount($myquery));
}
else
{
$this->set('Stocks',
$this->Stock->findAll("","","",100, $page));
$this->set('page',$page);
$this->set('total_stocks',$this->Stock->findCount());
}
/* clear the search */
/*$this->data = array(); */
}
else
{
$this->set('Stocks',
$this->Stock->findAll("","","",100, $page));
$this->set('page',$page);
$this->set('total_stocks',$this->Stock->findCount());
}
}
function cleanArray($array,$arrayToClean)
{
/* remove empty items in the array, so only fields
with search vales are kept. */
foreach ($array[$arrayToClean] as $index => $value)
{
if (empty($value))
unset($array[$arrayToClean][$index]);
}
return $array;
}
function buildRequest($array)
{
$tmpstring = '';
foreach ($array as $index => $value)
{
if (empty($tmpstring))
{
$tmpstring = $tmpstring . '"Product"."' .
$index . '" = ';
}
else
{
$tmpstring = $tmpstring . ' AND
"Product"."' . $index . '" = ';
}
if (ctype_digit($value))
{
$tmpstring = $tmpstring . $value;
}
else
{
$tmpstring = $tmpstring . '"' .
$value . '"';
}
}
return $tmpstring;
}
}
Bernard
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Cake PHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
