Jump to content


Bug in the search

search

  • Please log in to reply
31 replies to this topic

#21 connard

connard

    Member

  • Members
  • PipPip
  • 16 posts

Posted 09 March 2014 - 08:16 AM

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



#22 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 09 March 2014 - 08:43 AM

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 :(



#23 connard

connard

    Member

  • Members
  • PipPip
  • 16 posts

Posted 10 March 2014 - 03:41 PM

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 :)



#24 Dogtooth

Dogtooth

    Member

  • Members
  • PipPip
  • 10 posts
  • LocationGermany

Posted 12 December 2014 - 02:55 PM

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.



#25 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 14 December 2014 - 11:05 AM

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.



#26 Dogtooth

Dogtooth

    Member

  • Members
  • PipPip
  • 10 posts
  • LocationGermany

Posted 04 January 2015 - 03:27 PM

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.



#27 DrPaul

DrPaul

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts
  • LocationLondon

Posted 25 November 2015 - 03:45 PM

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?



#28 DrPaul

DrPaul

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts
  • LocationLondon

Posted 25 November 2015 - 06:06 PM

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 '!'


#29 DrPaul

DrPaul

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts
  • LocationLondon

Posted 25 November 2015 - 06:27 PM

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??



#30 DrPaul

DrPaul

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts
  • LocationLondon

Posted 01 December 2015 - 09:59 PM

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 :-(



#31 DrPaul

DrPaul

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts
  • LocationLondon

Posted 05 December 2015 - 08:06 PM

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.



#32 DrPaul

DrPaul

    Advanced Member

  • Members
  • PipPipPip
  • 38 posts
  • LocationLondon

Posted 05 December 2015 - 09:23 PM

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







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users