ActiveRecord, and something similar to and_where_like

[eluser]hugle[/eluser]
I'm building some search for my CI website, and came to a problem, don't know if it's easy to fix or not ...

I'll start with the problem:

I have an array of keywords I want to search:

Code:

Array
(
    [0] => wlan
    [1] => 2000
)

this is a list of keywords turned into array

here is snippet of my DB query generation:

Code:

foreach ($search as $value) {
            $this->ci->db->like('chipset.name', $value);
            $this->ci->db->or_like('drivers.driver_model', $value);
            $this->ci->db->or_like('os.name', $value);
            $this->ci->db->or_like('drivers.notes', $value);
            $this->ci->db->or_like('included.name', $value);
            $this->ci->db->or_like('drivers.hwid', $value);
        }

I thought it would generate smth similar to..
WHERE ... LIKE ...
or where LIKE ....
....
AND WHERE .... LIKE ...
I mean 'AND WHERE' would require previous keyword to exist,

But I get the query generated like:

Code:

WHERE  `chipset`.`name`  LIKE '%wlan%'
OR  `drivers`.`driver_model`  LIKE '%wlan%'
OR  `os`.`name`  LIKE '%wlan%'
OR  `drivers`.`notes`  LIKE '%wlan%'
OR  `included`.`name`  LIKE '%wlan%'
OR  `drivers`.`hwid`  LIKE '%wlan%'
AND  `chipset`.`name`  LIKE '2bit%'
OR  `drivers`.`driver_model`  LIKE '2bit%'
OR  `os`.`name`  LIKE '2bit%'
OR  `drivers`.`notes`  LIKE '2bit%'
OR  `included`.`name`  LIKE '2bit%'
OR  `drivers`.`hwid`  LIKE '2bit%'

there is no AND WHERE, but only AND, which starts search
both keyword over DB independently...

so If I search for a 'wlan' - I get one result:
live demo:
http://drivers.invista.lt/search/page/1/wlan

and if for a 'wlan 2000', I get many results, where is a WLAN or 2000
live demo:
http://drivers.invista.lt/search/page/1/wlan-2000

How can this be fixed?
maybe I should build queries myself...?

thanks!

[eluser]saidai jagan[/eluser]
I suggest building querys (no tot use Active Record Class for complex queries)

[eluser]jedd[/eluser]
saidai jagan's brief response belies the underlying reason for the suggestion - AR doesn't cope so well with groupings.

An interesting thread a little while ago - [url="http://ellislab.com/forums/viewthread/128918/"]new primitives for grouping using DMZ[/url] - touched on some of the problems that classic AR has once you want to get a bit more complex.

I very rarely use the AR calls, mostly because of the two main benefits (cleansing and db-agnosticism) are either replaced by a few other practices (global xss cleansing, is_numeric() or ->db->escape() functions) or negated as soon as you want to do anything slightly complex (sub-queries, any kind of and/or grouping).

There were hints and allegations that future versions of CI would allow more complexity in the construction of AR calls - but it might be quite a wait.

At the moment you have three broad approaches (as I see it):
1. stick with AR calls, and do your sub-selects manually, pulling data back into PHP to then generate the subsequent AR calls
2. use AR calls wherever you can, but drop into raw SQL queries, with your own sanity checking of input data, wherever you need to,
3. use your own hand-constructed SQL queries everywhere.

Note that 2 and 3 will (probably) tie you to a specific db engine, and the distinction between them is a matter of degree. I expect it mostly depends on whether you one day want to re-visit your code and AR-ize your custom SQL queries if and when the AR functions become available. Arguably, absent that intent, it might be more confusing to subsequent maintainers of your code if you have two different DB-access methods.

[eluser]saidai jagan[/eluser]
Thanx jedd

[eluser]hugle[/eluser]
[quote author="jedd" date="1258395350"]saidai jagan's brief response belies the underlying reason for the suggestion - AR doesn't cope so well with groupings.

An interesting thread a little while ago - [url="http://ellislab.com/forums/viewthread/128918/"]new primitives for grouping using DMZ[/url] - touched on some of the problems that classic AR has once you want to get a bit more complex.

I very rarely use the AR calls, mostly because of the two main benefits (cleansing and db-agnosticism) are either replaced by a few other practices (global xss cleansing, is_numeric() or ->db->escape() functions) or negated as soon as you want to do anything slightly complex (sub-queries, any kind of and/or grouping).

There were hints and allegations that future versions of CI would allow more complexity in the construction of AR calls - but it might be quite a wait.

At the moment you have three broad approaches (as I see it):
1. stick with AR calls, and do your sub-selects manually, pulling data back into PHP to then generate the subsequent AR calls
2. use AR calls wherever you can, but drop into raw SQL queries, with your own sanity checking of input data, wherever you need to,
3. use your own hand-constructed SQL queries everywhere.

Note that 2 and 3 will (probably) tie you to a specific db engine, and the distinction between them is a matter of degree. I expect it mostly depends on whether you one day want to re-visit your code and AR-ize your custom SQL queries if and when the AR functions become available. Arguably, absent that intent, it might be more confusing to subsequent maintainers of your code if you have two different DB-access methods.[/quote]

Thanks everyone for your input

And thanks for this info too
I was afraid that I should use self-builded queries...

If I came to some interesting sollution, I will let you guys know
thank you so much!

[eluser]hugle[/eluser]
Seems I didn't came with something SUPERIOR

I did $this->ci->db->escape_like_str for escaping my values...
and was building queries using php, and later:

Code:

$query = $this->ci->db->query($sql);
$result = $query->result_array();

now I have the results in usual style, as AR

Thanks guys again

thanks jedd for such a rich answer which just didn't leave any question out of my question

huglester