[connard]

Amit did you try what I experienced with a where clause killing flexigrid search ??

[Amit Shah]

well. can u share me table structure.. and the code.. i will love to experience it.. and if possible.. share a solution over it.. sorry was too bz completing my existing project work :(

[connard]

Thanks Amit! It is not bound to the table or structure, you can test if quick on one of your projects:

 

Add for example a

$this->grocery_crud->or_like('login',$row->login);

 

Then the Flexigrid search gets broken, it does not filter if you look for something, it looks like the "or_like" condition overload the Flexigrid search system. Tell me if I am clear... or not :)

[Dogtooth]

Hello,

When I did this:

 

I commented the following line and the issue was resolved... meaning - i eliminated the extra having clause for the same where clause

$this->basic_model->having($where[0],$where[1],$where[2]);

 

...the search worked, BUT it ignored the where(); instead it searched the whole table.

[Amit Shah]

well. can you print out the query that got generated... by using CI profiling so we can look into the same as what / where stuff c an be made up for / what went wrong..

cuz i have been using the same since the modification and never have had trouble with the same.

[Dogtooth]

Sorry but I don't know how to print out the query which is generated by the Flexigrid search. CI profiling only shows the inital Grocery query.

[DrPaul]

I've been struggling with the search bugs which occur when $crud->where() is used and it's driving me mad! :-)

 

A quick and dirty work-round for the dummy column problem which I use is to simply add the relevant fields to the database and comment them as being a dummy. Not elegant, but at least you don't need to hack core GC files.

 

I've applied every patch I've found on the forum or at GitHub to try and fix the search (thanks Amit!), working with a heavily-patched v1.4.1 of GC and have just about got something that seems stable.

 

So I thought - "time to move up to v1.5.2 and CI 3", and set about applying all my patches (e.g. actions on the left of the flexigrid list). It's working, but the search seems terminally broken.

 

I no longer get MySQL errors wrapped in HTML in the JSON responses when I do a search, which was happening before, but now the search more often than not incorrectly returns the entire data set whatever the search. Or sometimes it DOES work - I've yet to work out the logic. If I don't use ->where() then there is no problem.

 

This is worse than before - with no error messages I have no idea what is going wrong. Where do I patch GC to dump out the SQL being generated?

[DrPaul]

Ignore last line of my post - have worked out how to enable query logging on MySQL :-)

 

And this shows that GC seems to be outputting faulty queries which return the full data set when they shouldn't - when I cut and paste the query directly into MySQL it returns the "wrong" data. In fact, it's returning the data set which matches the ->where() setting in GC.

 

I'm testing by doing a Search All for something like "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" which certainly does not exist anywhere in the database, and GC is producing a query along the lines of:

 

SELECT *

FROM `orders`

LEFT JOIN `clients` AS `j62608e08` ON `j62608e08`.`id` = `orders`.`client`

LEFT JOIN `stock` AS `j447b7147` ON `j447b7147`.`id` = `orders`.`item`

WHERE (`shipped` IS NULL OR `shipped` = '0000-00-00') AND (`invoiced` IS NULL OR `invoiced` = '0000-00-00')

OR  `j62608e08`.`name` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `j447b7147`.`model` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `j447b7147`.`stock_descr` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `j447b7147`.`clr` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `ordered` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `pgn` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `pgd` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

OR  `order_notes` LIKE '%xxxxxxxxxxxxxxxxx%' ESCAPE '!'

[DrPaul]

Doh!

 

Re-reading the SQL it's obvious! But how to fix it?

 

GC is simply appending every search item as an OR statement after the WHERE clause.

 

Doesn't matter than none of my OR clauses match anything, the WHERE clause returns the data anyway.

 

Surely GC should be wrapping the appended OR statements in an AND statement??

[DrPaul]

Oh dear, this one's a bit of a deal-breaker isn't it?

 

The problem goes all the way down to the CodeIgniter query builder class and the way in which GC is using it.

 

GC is calling ci->where() when you have a where() defined.

 

Do a search from that list, and GC makes repeated calls to ci->or_like() which CodeIgniter just chains together.

 

Result? Search in GC is completely broken if you use ->where()

 

This looks like a structural problem in GC and I can't see it being fixed any time soon :-(

[DrPaul]

OK I've been able to code round the problem - at least for Search All, it's work in progress.

 

The only changes are to the set_ajax_list_queries() function, which Amit has already modded in this thread.

 

If anyone is interested I can upload the revised function.

[DrPaul]

GC search code also failed if a where() was used and you searched against a column which uses set_relation() with more than one link

 

Have fixed that as well.

 

The function is available on GitHub under issue #341