CODEWITHSUNDEEP
mysqlcodeigniterdatatables
shivamsupr
shivamsupr

Reputation: 480

How do I perform mysql NOT IN operation in jquery datatables in codeigniter?

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?

Upvotes: 0

Views: 186

Answers (1)

Ahmed Gaber
Ahmed Gaber

Reputation: 708

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

Upvotes: 1

Related Questions