• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
DB Active Record: where / or_where return different result

#1
[eluser]tkaw220[/eluser]
Hi,

When working with Active Record, I found below issue:

1)
Code:
$q = $this->db
           ->where('format', 'reader')
           ->or_where('format', 'reader with 3D goggles')
           ->order_by('brand, format, title', 'asc')
           ->get('products');

Query:
Code:
SELECT * FROM (`products`) WHERE `format` = 'reader' OR `format` = 'reader with 3D goggles' ORDER BY `brand`, `format`, `title` asc

Return products properly.

2)
Code:
$q = $this->db
           ->where('format', 'reader')
           ->or_where(array('format' => 'reader', 'format' => 'reader with 3D goggles'))
           ->order_by('brand, format, title', 'asc')
           ->get('products');

Query:
Code:
SELECT * FROM (`products`) WHERE `format` = 'reader' OR `format` = 'reader with 3D goggles' ORDER BY `brand`, `format`, `title` asc

Return only products match the second parameter, which is reader with 3D goggles.

I do not understand how two codes generated one same SQL query, but return result differently. Would appreciate if someone could clarify this.

Thank you for your time.

#2
[eluser]toopay[/eluser]
Your second query should returning different SQL statement, something like
Code:
SELECT * FROM (`products`) WHERE `format` = 'reader' OR `format` = 'reader' OR `format` = 'reader with 3D goggles' ORDER BY `brand`, `format`, `title` asc
But indeed, in this case, this should generate same result with previous query.

#3
[eluser]tkaw220[/eluser]
Hi toopay,

Unfortunately, My second query return the same query as the first code. I copied the result directly from my web browser. By the way, I am using CI 2.0.2.

#4
[eluser]toopay[/eluser]
To check all the db object, in your controller,
Code:
var_dump($this->db);
And as i stated, if you write an active record statement like
Code:
->where('format', 'reader')
->or_where('format', 'reader with 3D goggles')
will generates different SQL statement, against
Code:
->where('format', 'reader')
->or_where(array('format' => 'reader', 'format' => 'reader with 3D goggles'))
event we already know that both is should have/generates same result in its logic.

Post your sql dump file here, so anyone can check that. Because if it goes like you said, it can considered as inconsistent or bug.

#5
[eluser]tkaw220[/eluser]
Hi toopay,

Thank you for your time. Firstly, I apologize for my huge mistake. The second code should read:

Code:
$q = $this->db
           ->or_where(array('format' => 'reader', 'format' => 'reader with 3D goggles'))
           ->order_by('brand, format, title', 'asc')
           ->get('products');

The sql dump for first code:

Code:
array
    1 => string 'SELECT DISTINCT `format`
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `format` asc' (length=129)
      2 => string 'SELECT DISTINCT `language`
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `language` asc' (length=133)
      3 => string 'SELECT DISTINCT `brand`
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `brand` asc' (length=127)
      4 => string 'SELECT *
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `brand`, `format`, `title` asc' (length=131)

And the sql dump for the second code:

Code:
array
1 => string 'SELECT DISTINCT `format`
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `format` asc' (length=129)
      2 => string 'SELECT DISTINCT `language`
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `language` asc' (length=133)
      3 => string 'SELECT DISTINCT `brand`
FROM (`products`)
WHERE `format` = 'reader'
OR `format` = 'reader with 3D goggles'
ORDER BY `brand` asc' (length=127)
      4 => string 'SELECT *
FROM (`products`)
WHERE `format` = 'reader with 3D goggles'
ORDER BY `brand`, `format`, `title` asc' (length=108)

Again, the first code return both reader and reader with 3D goggles, whereas the second code return only the reader with goggles.

I am very sorry for my mistake in my first post.

Thank you.

#6
[eluser]toopay[/eluser]
[quote author="tkaw220" date="1307016013"]Hi toopay,

Thank you for your time. Firstly, I apologize for my huge mistake. The second code should read:

Code:
$q = $this->db
           ->or_where(array('format' => 'reader', 'format' => 'reader with 3D goggles'))
           ->order_by('brand, format, title', 'asc')
           ->get('products');

[/quote]

Your above last query, since there are no "->where" statement before "->or_where" statement, should generates
Code:
SELECT * FROM (`products`) WHERE `format` = 'reader with 3D goggles' ORDER BY `brand`, `format`, `title` asc
And indeed, theres something wrong here Wink You may asked, "WHY"? Its simply because your array parameter, contain same key (in this case, 'format'), so if you passed an array like this
Code:
array(
  'format' => 'foo',
  'format' => 'bar'
)
The Active record class, will process it like below
Code:
// remember, your passed array is "array('format' => 'foo', 'format' => 'bar')"
foreach ($key as $k => $v)
{
   //on first loop, this will generates $k = 'format' and $v = 'foo'
   //But on second loop, this will generates $k = 'format' and $v = 'bar'
}
// and as the result, now we have $k = 'format' and $v = 'bar' here
So since you have same key name, then the last key and its value, replace the previous key and value. You should use "->where" before "->or_where", to avoid this pitfall.

In addition, for your convenience in debugging database query instance, you can print out the query, by put $this->db->queries on your controller which calls the model, something like this :
Code:
// On your controller
function test_query()
{
   // Load the model
   $this->load->model('foo');
  
   // Execute some model's function
   $this->foo->bar('some_params');
   // get the queries instance of above function
   var_dump($this->db->queries);

   echo '<br/>';

   // Execute another model's function
   $this->foo->anotherbar('some_params');
   // get the queries instance of above function
   var_dump($this->db->queries);

}

#7
[eluser]tkaw220[/eluser]
Hi toopay,

Now I got it all. Thank you very much for the detailed explanation. But I couldn't find $this->db->queries inside CI help. Is it an undocumented function?

In the meantime, please allow me to query on your Proxy Library. Previous I came across with an issue which I tried to describe in short below:

1) My shopping cart sent _POST values via cURL to payment gateway which I believe does not support REST. The payment gateway received all _POST values successfully, but it does not fully redirect to their web site (where the URL is stick on my domain, while displaying partial of their page) >>> more detail here http://ellislab.com/forums/viewthread/187584/.

2) Can I solve this problem with your library since your library utilize pure PHP, not cURL?

#8
[eluser]toopay[/eluser]
There are other member which request POST feature at my proxy library, since for now it just support GET. But i'm really out of time for these months, and that extra feature should wait until i have some free time.

But related with your problem with gateway, i'm sure there are some workaround for it, with cURL. Digging around at curl.haxx.se, and you should able to find a solution for your (specific) problem. If you cant achieve something with cURL, then you're out of luck, since cURL is the most fancy stuff for to connect and communicate to many different types of servers with many different types of protocols, in PHP.

If likened to the building, my lib is like a house, while the cURL is a 20th floor building. I create that library, just for portability and simplicity reasons (means, it is for someone who doesnt have curl extension installed). Beyond that, in fact nothing could be achieved with my Proxy Library, if that person cant achieve with cURL. Because, in the bottom of all both building, they have same fondation.

It's just that, the proxy library could be an alternative for people who like portability, simplicity and some kind of "primitiveness" attraction on their code ;-)

#9
[eluser]tkaw220[/eluser]
Hi toopay,

Well noted. I will keep an eye on the development of your library. Meanwhile, I couldn’t find $this->db->queries inside CI help. Is it an undocumented function?

Again, thank you.

#10
[eluser]toopay[/eluser]
tkaw220, you shouldnt worried about that at all. Since all of these, is about Object Oriented approach, then you can access all CI super object, and sub-object. When you put '$this' on some controller, actually it represent CI super object.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.