1.7.0 handling mysql queries differently ... breaking a few of mine

[eluser]skattabrain[/eluser]
So this used to work on 1.6.3, it should do the following ...

get from the table 'entries' that have 'active' = 1 that also have either 1 of the 2 items ...

entries.user_id = '.$this->session->userdata('user_id')

OR

entries.public = "1"

Code:

$this->db->from('entries');
$this->db->where('entries.active', '1');

$this->db->where('(entries.user_id = '.$this->session->userdata('user_id').' OR entries.public = "1")');
    
$this->db->join('projects', 'projects.project_id = entries.project_id');
$this->db->join('ci_users', 'ci_users.user_id = entries.user_id');
$this->db->order_by('entries.entry_id', 'desc');
$this->db->limit($limit);
$query = $this->db->get();

this is the code it creates ...

Code:

SELECT * FROM (`entries`)
JOIN `projects` ON `projects`.`project_id` = `entries`.`project_id`
JOIN `ci_users` ON `ci_users`.`user_id` = `entries`.`user_id`
WHERE `entries`.`active` = '1'
AND `(entries`.`user_id` = 3 OR entries.public = "1") ORDER BY `entries`.`entry_id` desc LIMIT 25

this is what it should create ...

Code:

SELECT * FROM (`entries`)
JOIN `projects` ON `projects`.`project_id` = `entries`.`project_id`
JOIN `ci_users` ON `ci_users`.`user_id` = `entries`.`user_id`
WHERE `entries`.`active` = '1'
AND (`entries`.`user_id` = 3 OR entries.public = "1")
ORDER BY `entries`.`entry_id` desc LIMIT 25

[eluser]skattabrain[/eluser]
btw ... i have several queries no longer working. i use active record but probably do things it wasn't intended too ... this also no longer works ...

Code:

$this->db->select('ingredient_item.ingredient_id,
ingredient_to_product.ingredient_id as liveing_id,
ingredient_item.ingredient as name,
ingredient_type.ingredient_type,
ingredient_to_product.product_id');
$this->db->from('ingredient_item');
$this->db->join('ingredient_type', 'ingredient_type.ingredient_type_id = ingredient_item.ingredient_type_id');
$this->db->join('ingredient_to_product', 'ingredient_to_product.product_id =  '.$product_id.' AND ingredient_to_product.ingredient_id =  ingredient_item.ingredient_id');
$this->db->order_by('ingredient_type.ingredient_type');
$this->db->order_by('ingredient_item.ingredient');
$query = $this->db->get();                
return $query;

what it creates ...

Code:

SELECT `ingredient_item`.`ingredient_id`, `ingredient_to_product`.`ingredient_id` as liveing_id, `ingredient_item`.`ingredient` as name, `ingredient_type`.`ingredient_type`, `ingredient_to_product`.`product_id`
FROM (`ingredient_item`)
JOIN `ingredient_type` ON `ingredient_type`.`ingredient_type_id` = `ingredient_item`.`ingredient_type_id`
JOIN `ingredient_to_product` ON `ingredient_to_product`.`product_id` = `1001` AND ingredient_to_product.ingredient_id = ingredient_item.ingredient_id
ORDER BY `ingredient_type`.`ingredient_type`, `ingredient_item`.`ingredient`

[eluser]thespy[/eluser]
You should look at this thread (bug report), seems to be the same problem : http://ellislab.com/forums/viewthread/94808/

[eluser]skattabrain[/eluser]
thanks ... i posted my issues there.