0

I want to do NOT IN operation or subquery on dataTables, but I'm able to configure how to actually perform such operation. Is it even possible to perform such operation?

Here is my query : 

->select("t_geocode.form_no,t_store.name ,CONCAT(t_store.address,'',t_location.address) AS locaddress,t_admin.username,t_geocode.creation_date,t_geocode.notes IS NOT NULL,t_geocode.latitude,t_geocode.longitude", FALSE)
                ->from("t_geocode")
                ->join("t_store", "t_store.form_no = t_geocode.form_no")
                ->join("t_location", "t_location.id = t_store.area")
                ->join("t_admin", "t_admin.user_id = t_geocode.created_by")
                ->where("t_geocode.is_discarded", 0)
                ->where("t_geocode.is_pending", 1)
                ->where_not_in("t_geocode.form_no","t_store.form_no")
                ->add_column("username", $usernameBar, 't_admin.username,t_geocode.creation_date,t_geocode.notes IS NOT NULL')
                ->add_column("coordinates", $cordinateBar, 't_geocode.latitude,t_geocode.longitude')
                ->add_column("actions", $actionLinkBar, 't_geocode.form_no')
                ->unset_column("t_store.name")
                ->unset_column("t_store.address")
                ->unset_column("t_location.address")
                ->unset_column("locaddress")
                ->unset_column("t_admin.username")
                ->unset_column("t_geocode.creation_date")
                ->unset_column("t_geocode.notes IS NOT NULL")
                ->unset_column("t_geocode.latitude")
                ->unset_column("t_geocode.longitude");

I need to perform NOT IN operation on t_geocode and t_store tables form_no field. Can someone help?

madth3
  • 7,275
  • 12
  • 50
  • 74
shivamsupr
  • 470
  • 1
  • 6
  • 16
  • I know nothing about CodeIgniter, but a standard workaround for `NOT IN` (actually favored because it's nearly always faster) is to `LEFT JOIN` to the other table and omit where the other table's PK value is NULL, meaning that it didn't have a match and therefore it isn't "in". Hope that makes sense and it's something CodeIgniter can do if it can't do "not in". There are quite a few SO postings about this; the first one I found was [here](http://stackoverflow.com/a/1519333/2091410). – Ed Gibbs Jun 14 '13 at 13:15
  • But even left join isn't work in DataTables – shivamsupr Jun 14 '13 at 13:57
  • I'm afraid that was all I had to offer; sorry it didn't work out. I wish I could help, but not knowing CodeIgniter I can't. The only other advice I can give is to try working out the query in MySQL Workbench or the command line, then translate to CodeIgniter once it's working. Best of luck! – Ed Gibbs Jun 14 '13 at 14:01

1 Answers1

1

A workaround for this that may suit your case is to create MYSQL VIEW with all conditions you need to display and select directly from the view as i can see your are unset a lot of columns,

hope that helps

Ahmed Gaber
  • 707
  • 1
  • 10
  • 27