Category : query-builder

I wanted to join table NotificationTypes with UserEnabledNotificationTypesId on the NotificationTypesId column of UserEnabledNotifications , only for the rows in which deleted_at column of NotificationTypes table is null. I’ve tried as below. But it is not sorting for the null value of deleted_at column in NotifocationTypes table. Any solutions? UserEnabledNotifications::with(‘userNotications’, function ($query){ $query->whereNull(‘deleted_at’); })->select(‘userId’,’notificationTypesId’)->get(); UserEnabledNotifications ..

Read more

I have an $attendace variable contains a collection from a Laravel query builder: $attendace = [ { "row_id":65, "emp_number":"IPPH0004", "time_stamp":"01:00:00", "attendance_status":"Punch In", "date_created":"2021-10-02" }, { "row_id":68, "emp_number":"IPPH0004", "time_stamp":"07:30:00", "attendance_status":"Start Break", "date_created":"2021-10-02" }, { "row_id":69, "emp_number":"IPPH0004", "time_stamp":"08:00:00", "attendance_status":"End Break", "date_created":"2021-10-02" }, { "row_id":70, "emp_number":"IPPH0004", "time_stamp":"08:30:00", "attendance_status":"Start Break", "date_created":"2021-10-02" }, { "row_id":71, "emp_number":"IPPH0004", "time_stamp":"09:00:00", "attendance_status":"End Break", "date_created":"2021-10-02" ..

Read more

I am trying to get the value from a collection in PHP. $todaylog variable contains a collection from a laravel query builder: $todaylog = [ { "row_id":55, "emp_number":"IPPH0004", "timestamp":"03:30:23", "attendance_status":"Punch In", "date_created":"2021-10-01" }, { "row_id":56, "emp_number":"IPPH0004", "timestamp":"11:32:50", "attendance_status":"Start Break", "date_created":"2021-10-01" }, { "row_id":57, "emp_number":"IPPH0004", "timestamp":"11:33:09", "attendance_status":"End Break", "date_created":"2021-10-01" } ] What I have done so ..

Read more

Hello I want to get from two tables (table of articles and a table of images) distinct values on a non-key field with pagination, I am executing this code in laravel: $articles = DB::table("articles AS a") ->select("a.id","a.title","a.text", DB::raw("(SELECT article_imgs.img_link FROM article_imgs WHERE a.id = article_imgs.article_id order by article_imgs.article_id limit 1) as img_link")) ->pagination(6); However I ..

Read more

How can I write this query with Laravel Eloquent "SELECT * FROM links WHERE (user_id = 1 OR user_ip = ‘127.0.0.1’) AND id = 53 LIMIT 1" I have written this one: ` $link = Link::query() ->where(‘user_id’, Auth::id()) ->orWhere(‘user_ip’, request()->ip()) ->with(‘clicks’) ->findOrFail($id); And the SQL version of this one is :"SELECT * FROM links WHERE ..

Read more

I have a query let’s take a look, DB::table(‘states’) ->join(‘brands’,’brands.state_id’,’=’,’states.state_id’) ->join(‘cities’,’cities.state_id’,’=’,’states.state_id’) ->select( ‘state_name’, DB::raw("(COUNT(‘cities.city_id’)) as cities"), DB::raw("count(‘brands.brand_id’) as brands")) ->groupBy(‘state_name’) ->get(); I want to get the state_name from the states table & counts the cities and brands under the state. But in these queries, the problem is city counts & brand counts show the same ..

Read more

I have a query let’s take a look, DB::table(‘states’) ->join(‘brands’, ‘brands.state_id’, ‘=’, ‘states.state_id’) ->join(‘cities’, ‘cities.state_id’, ‘=’, ‘states.state_id’) ->select( ‘state_name’, DB::raw("(COUNT(‘cities.city_id’)) as cities"), DB::raw("count(‘brands.brand_id’) as brands") ) ->groupBy(‘state_name’) ->get(); I want to get the state_name from the states table & count the cities and brands under the state. But in these queries, the problem is city ..

Read more

i have an couloum in database in which i store array to store device specification in an array i think its array in a array now i want to get devices where for example technology 5G is Yes Value here is the below code and database entry of array Array Of Specs in database a:12:{s:6:"Launch";a:2:{s:9:"Announced";s:12:"2019, ..

Read more

I need response like this. "result": [ { "properties": { "device_id": 15196, "device_name": Street Light 1, "state" : 1, "status": 1, }, "geometry":{ "lat":33.7017, "lng": 73.0228 } }, { "properties": { "device_id": 15196, "device_name": Street Light 1, "state" : 1, "status": 1, }, "geometry":{ "lat":33.7017, "lng": 73.0228 } }, ] where my code is below. ..

Read more

i am writing a query for getting all the type which have properties DB::table(‘property_types’) ->join(‘properties’,"property_types.id",’=’,"properties.property_type") ->select(‘property_types.id’,’property_types.types’) ->get(); column and all related field inside the table but it’s throwing error SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘properties.property_type=property_types.id’ in ‘on clause’ (SQL: select * from `property_types` inner join `properties` on `properties`.`property_type=property_types`.`id` = “) Sourc..

Read more

I have a Book model with the following relationships defined: class Book extends Model { public function bookfiltertopics() { return $this->belongsToMany(‘AppBookFilterTopic’, ‘book_bookfiltertopic’, ‘book_id’, ‘bookfiltertopic_id’); } public function bookfilterformats() { return $this->belongsToMany(‘AppBookFilterFormat’, ‘book_bookfilterformat’, ‘book_id’, ‘bookfilterformat_id’); } } With my BookFilterTopic model below: class BookFilterTopic extends Model { protected $table = ‘bookfiltertopics’; public function books() { return ..

Read more

I’m trying to figure out if its possible to add a key that is not a table’s column. query code: $list = DB::table(‘ouin_request_base as base’) ->select( ‘base.request_id’, ‘users.last_name as last_name’, ‘users.first_name as first_name’, ‘division.name as division_name’, ‘base.created_at’, ‘status.name as status_name’ ) ->join(‘users’, ‘base.user_id’ , ‘=’, ‘users.id’) ->join(‘ouin_request_data as data’, ‘base.request_id’, ‘=’, ‘data.request_id’) ->join(‘m_request_status as status’, ..

Read more

I’m trying to figure out if it’s possible to add a key that is not a table’s column. Query code: $list = DB::table(‘ouin_request_base as base’) ->select( ‘base.request_id’, ‘users.last_name as last_name’, ‘users.first_name as first_name’, ‘division.name as division_name’, ‘base.created_at’, ‘status.name as status_name’ ) ->join(‘users’, ‘base.user_id’ , ‘=’, ‘users.id’) ->join(‘ouin_request_data as data’, ‘base.request_id’, ‘=’, ‘data.request_id’) ->join(‘m_request_status as status’, ..

Read more

SELECT course_name,course_fee, course_id,COUNT(*) as total_venue from (SELECT courses.title_bn as course_name,courses.course_fee as course_fee, course_id,publish_courses.institute_id,branch_id,training_center_id, count(course_id) AS total_course_id FROM publish_courses join courses on courses.id = publish_courses.course_id GROUP BY course_id, institute_id, branch_id, training_center_id) as publish_courses_vertual_table group by course_id Sourc..

Read more