Convert DB Query to eloquent

I am trying get data from 3 tables My tables: table 1: bookings Id, dog_id service_id user_id table 2: services id service_name table 3: dogs id dog_name This is my current DB query which is work but I want laravel Eloquent in mysql Dog Controller: $pendingBookingInfo = DB::table('bookings') ->select('*') ->leftJoin('d

azizulislam's avatar
azizulislam's avatar piljac1Eloquent
5

Spatie Query Builder combined search

I'm using the Query Builder for building a search functionality for a index table. My members table has a 'first_name' and a 'last_name' column, which are populated separetly. For the search functionality, I want my users be able to search for the 'first_name' and 'last_name' combined as full_name. On my Member Model, there is a function fullName() which is giving the correct f

blixary's avatar
blixary's avatar blixaryEloquent
2

How can I improve the performance of Query Builder?

laravel : 10 mysql 8.0 have 2 server mysql 1 for write 2 core , 4 Ram , 250GB NVME 1 for read 2 core , 4 Ram , 250GB NVME in transactions table : 6 million record They are increasing on a daily basis $table->boolean('is_maker')->index()->default(false); $table->integer('market_id')->index(); $table->index(['created_at']); database.php

samehdev's avatar
samehdev's avatar samehdevEloquent
12

Can we cache query run on pagination to count the results?

Can we cache query run on pagination to count the results. How can we do that? any plugins? I don't want any query builder approach as it could impact all the models or queries in my existing system.

vinay8vee's avatar
vinay8vee's avatar vinay8veeEloquent
6

How to show only those values which have status=0 in eloquent query in laravel?

I have to work on two of my main models Assessment and User where I have a hasOne relation, in which assessments table have created_by as foreign key from users table. I need to fetch only those assessments whose status are 0 also of the users whose status are 0. The issue I am facing with my query is it is returning all of the assessments, if the users status is not 0 then als

babai9's avatar
babai9's avatar babai9Laravel
2

PHPStan and Custom Query Builder

Hi Laravel and PHPStan experts. Is it somehow possible to make PHPStan recognise usage of $this keyword that is referencing a Model instance from within a Custom Query Builder? Please see the illustration below. Thanks! use Illuminate\Database\Eloquent\Model; class WorkOrder extends Model { // ... public function assignments() { return $this->hasMany(

Salvis's avatar
Salvis's avatar SalvisEloquent
1

Are filament stand alone tables compatible with Spatie Query Builder?

I would like to build a table on my website (outside Filament admin panel) that is populated by URL requests. I have implemented this using Spatie Query Builder , so that I get a table based on the url request such as Spatie's example below: /users?filter[name]=John: I have also been able to use a filament table outside of the admin panel which appears functional (although its

mcalonje's avatar
mcalonje's avatar LaryAILaravel
1

Is it possible to print out the eloquent query ?

Hello Is it possible to display the eloquent query like we do with the raw SQL (->tosql())? I ask this question because my eloquent request is dynamic and I build it by using many other classes Thanks

GodziLaravel's avatar
GodziLaravel's avatar SnapeyGeneral
7

Livewire pagination adding form fields to query string

Hello, I have a livewire page with some filters at the top of the page long with an 'Apply Filters' button. These all work as expected with no issues. I am also including the query string so users can bookmark filtered results for later use, this work sa expected. Issue is with the pagination, If I for instance change the date, select something, add any text to the search filed

RayC's avatar
RayC's avatar LaryAILivewire
1

Skipping elements in a query

In this Livewire code I have a query redundancy so that I can indicate which id the query should start from, however, this creates a repetition of the query for me. My homepage to make you understand is structured like this: 10 articles order desc other element More articles, skipping those 10 from before. the code I'm using now is: $articoli = Articoli::orderByDesc('

Xanger's avatar
Xanger's avatar SnapeyLivewire
3

Laravel API + React Query Filter, Sort, Paginate

I have simple app with search, filter, sort and pagination. Can someone give me feedback if everything is ok or maybe something should be improved. In Laravel Controller: public function index(Request $request) { $perPage = $request->get('perPage', 10); return Post::with(['user:id,name', 'category:id,name']) ->when($request->filled('category'), functio

Dommmin's avatar
Dommmin's avatar DommminReact
0

Convert query builder to eloquent

Hello guys, i have a task where i need to change from query builder to eloquent pagination. This is the code: $draw = (int)$request->input('draw'); $columns = $request->input('columns'); $length = (int)$request->input('length'); $start = (int)$request->input('start'); $skip = $length * $start; $search = $request->input('search'); How will the pagination look like

alex2112's avatar
alex2112's avatar alex2112Eloquent
2

Query Builder count

Hi guys help me a little! I use laravel Query Builder. I have posts and replies table. I need total replies in the post(not use get(), use first() or find()). Is it possible or not. $post= DB::table('posts') ->leftJoin('replies', 'posts.id', '=', 'replies.post_id') ->select( 'posts.id', 'posts.slug',

MarrsGreen's avatar
MarrsGreen's avatar tangtangLaravel
6

Group query result by type and calculate sum of each group.

I have this query $data= DB::table('expenses') ->select('amount', 'type')->get(); now I want to group each expense by their type and calculate the sum of all the amount that have the same type. How do you think I should go about this?

j_watson's avatar
j_watson's avatar j_watsonLaravel
2

Strange query results with `when` and `where`

Hello, I have a strange issue a query I'm using to show a list of "members" in my application. It's a sports association site, with clubs and members belonging to clubs (via club_id field). Each club has a page where I list its members. I'm using Livewire component for that, as I have search box to filter by member's name or their membership number. The issue I have i

redlik's avatar
redlik's avatar redlikEloquent
4

Sql query incorrect with getModel()->{relationship_name}

Hello, I'm running into an issue while trying to build a dynamic query for a relationship. For a bit of context, I'm using Spatie Query Builder and want to create a custom Sort that allows to sort by nested relationships and working as well with hasOne()->ofMany() or things like that. I could almost get it to work but as soon as I call a more complexe relationship, I get a w

Nonoo's avatar
Nonoo's avatar NonooEloquent
3

HasMany Relation not null query

I have a table with >400M rows which is partitioned by date and all queries use date. However, using a hasMany relation causes laravel to do this query select * from `table` where `table`.`relation_id` = x and `table`.`relation_id` is not null This takes a few seconds. Is there a way to avoid this. Currently I'm not using the relation but querying the table direct.

flarpy's avatar
flarpy's avatar AlexanderW...Eloquent
2

Using variables stored as integers inside of Eloquent Query

I have been looking through Larvel's documentation, and wondering if it is possible to use a stored variable with the value of an integer inside of a query. For instance: public $x = 3; Query1::latest()->take($x)->get(); This displays an error as $x being undefined, even tho I declared it publicly above.

Jdubstep1357's avatar
Jdubstep1357's avatar kokoshnetaLaravel
3

passing search parameters as url path instead of query string

Hi all, This must have been asked thousands of times but I'm not sure why my searching isn't coming up with anything. I'd like to be able to pass search parameters as part of the url path instead of as a query string. So these are all valid: /posts /posts/user/someusername /posts/tag/sometag /posts/user/someusername/tag/sometag /posts/tag/sometag/user/someusername I was hoping

bwrigley's avatar
bwrigley's avatar bwrigleyLaravel
12

From mysql to eloquent query

Good morning, I also wrote stackoverflow to get help regarding the conversion of a query that I'm trying to execute, if I run it in phpmyadmin it works perfectly but when I convert it for Laravel I get errors, I tried to use two conversion tools also online but I get the same result can anyone help me resolve this error? SELECT ticket, SUM(MINUTE(time_stamp)) AS minutes, SU

marcoplus's avatar
marcoplus's avatar marcoplusLaravel
3

Sanctum query

Hi, I am using laravel sanctum to authenticate my api requests from my standalone frontend. On my frontend, sometimes on mounting the page I need to call a few different endpoints from the server, however with sanctum, it gets the authenticated user via running a db query first select * from "users" where "id" = 1 limit 1. This query is quite slow,(for testi

colin-tech's avatar
colin-tech's avatar vincent150...Laravel
10

Cast local key in morphMany relationship before query

I am having a problem with a query in MySQL 8 as it's not using the index as it should. I have two tables, one is users and the other is conversions. users (id bigint unsigned) conversions (id bigint unsigned, actable_type varchar(191), actable_id varchar(36), INDEX actable_id) Then, in the User class, I have the relationship defined as public function conversions(): Morph

afpr252's avatar
afpr252's avatar afpr252Laravel
0

query builder distinct() it doesn't not working

so i want convert query from sql to laravel builder SELECT DISTINCT YEAR(publish), research_group_id FROM research_group_statuses WHERE research_group_id = 1 AND status = 'basic' ORDER BY publish ASC; Is this correct? because distinct() doesn't work at all $filterOne = ResearchGroupStatus::query() ->whereIn('status', ['Basic'])->where('research_group_id', 1)

mhmmdva's avatar
mhmmdva's avatar s4muelLaravel
2

how to convert query builder

i want to convert query from sql to eloquent code to eloquent but i can't . because I have to use the foreign key from research_group_id my query builder is following SELECT * FROM research_group_statuses WHERE research_group_id = 1 AND status = 'basic';

mhmmdva's avatar
mhmmdva's avatar mhmmdvaLaravel
12

Query Returns Empty Relationship When Data Exists

Hi All, I'm running into an issue where some of the next_instance relationships return empty despite the data being present in the database. When I write the raw SQL in DataGrip, I am able to find what I'm looking for, but when querying via Eloquent, I'm running into issues. The first record returned (9407707) is behaving as desired, but the rest of the records are not. If ne

onairmarc's avatar
onairmarc's avatar jlrdwEloquent
3

Laravel Query time casting for variables

Hi there, we have certain tables where the column type is a string but sometimes devs miss that and use a variable that might have an int type. This results in a heavy query time as the index doesn't work. Instead of every time using (string) casting, is there a way to by default change the casting to string whenever that specific column is used in any query?

shiprocket_devs's avatar
shiprocket_devs's avatar krisi_gjik...Eloquent
8

Keep existing Query String

Are there any clever ways of preserving query strings when I have a page mixed up with tables (column sort), search and pagination. All 3 different components working together. I would rather keep it as they are not aware of each others query string parameters. It seems like withQueryString() is only working for pagination, so when i sort by a column the pagination and search e

EmilMoe's avatar
EmilMoe's avatar EmilMoeInertia
1

laravel query

so i have this table fsos with these columns - id - created_by - name ..... another table named fso_items with these columns - id - fso_id - service_id - serviceable_id - serviceable_type and another table called com_transfers with these columns - id - fso_item_id - from_type - from_id - to_type - to_id so i am creating an api call for the f

maska's avatar
maska's avatar iamsagarva...Laravel
1

how to use where query a in casted array field

Hello friends, how to use where query a in casted array field for EXAMPLE in model protected $casts = [ 'supplier_ids' => 'array', ]; Data Stored in Database table field in this format supplier_ids [1,2,3,4] // db table field data stored like thats [1,2,3,4] [1,3,5,7] AND NOW I need LIST ALL DATA query only if supplier_ids field where casted array = 3 I

Respect's avatar
Respect's avatar Tray2Eloquent
9

Combing models into a query

I am trying to combine models into a single query. I made a dependent dropdown upon click shows other tables. However, for whatever reason, after the page displays the other table, the only data being displayed is whatever data is inside the current model. Here is what everything looks like BEFORE anything is pressed: https://imgur.com/WO1aVyc Here is my first backend query: ht

Jdubstep1357's avatar
Jdubstep1357's avatar Jdubstep13...Livewire
22

Eloquent query comparing two related models

Let's say I have an order model with two address models, shippingAddress and billingAddress. I want to select all orders where the street_address in both shippingAddress and billingAddress are the same. Order::whereHas('billingAddress', function(Builder $query) { $query->where('street_address', function(Builder $query) { // raw statement? } } stumbling on how to get

johnvoncolln's avatar
johnvoncolln's avatar kevinbuiEloquent
15

Custom relationship query

Hi All, We're currently building an application for a client who wants automatic parcel pickup assignments based on Dutch postcodes, e.g 1018DR. I have a model Account, in this model i have the properties id, postcode and country id: 1. postcode: 1018DR. country: NL id: 2. postcode: 1014BB. country: NL I have a model Range, in this model i have the properties pickup_point_id,

dannydehaan's avatar
dannydehaan's avatar dannydehaa...Eloquent
10

How can I add query to an existing query from a variable?

Hello, If I have a function with the base query, and I want to pass extra query as a variable: function updateQuery($extra_query) { $base_query = Model::where(...); // $base_query->(add $extra_query)->get(); } For example if I want to add where('col_x', '=', $val) and pass that to the function, how can I do that? Thanks

Ligonsker's avatar
Ligonsker's avatar jlrdwCode Review
3

withCount N+1 query

I had a query like this, and Sentry detects it as an N+1 problem. what's the better way to do it?, I might use $appends but I don't want to count them everywhere I use the Brand model. $this->brands = Brand::active() ->withCount(['ads' => function ($query) { $query->active(); }]) ->orderBy('ads_count', '

Chaeril's avatar
Chaeril's avatar ChaerilLaravel
7

Routes stop working when a query is added in the appServiceProvider

Hi guys, I am newbie to Laravel and this forum. I am currently learning Laravel (using version 10) and I have come across a problem. I want to have access to my menu on all front pages and I created the query in my appServiceProvider file like this: $lang = request()->segment(1); if (!empty($lang) && $lang !== 'admin') { $activeLang =

Golavt's avatar
Golavt's avatar vincent150...Laravel
5

Moving from service to... Action (Eloquent query)

Hi, What about something like that ? I had a method inside my service class, the method returns this : Tag::popular()->take($total) What about Eloquent query inside Action ? How can i do that ? Juste use Tag::popular()->take(15) inside my controller ? It sound like a bit overkill to have an Action class for this... Thank you for your help !

bloup's avatar
bloup's avatar bloupLaravel
14

Slow query because of huge where in clause

Hey everybody! I have an app with many employees which belong to different companies. Then I have supervisors who can access the data of multiple companies. I do have 200,000 employees in the database (including soft deleted) and another 5000 companies. My query looks like this. Select * from employees where company_id in (22, 24, ....., 3000) and empoyees.deleted_at is null or

massimo's avatar
massimo's avatar JussiManni...General
6

Query String won't clear when empty data object

I've done this on the past and I don't remember ever struggling with this, but maybe I did it different. I have my regular index controller, returning an inertia view, everything wotks perfect. My issue comes when I add filters. I'm using a watcher on a reactive filters object to trigger a page reload, where my controller will catch it and filter the data accordingly. Everyth

undeportedmexican's avatar
undeportedmexican's avatar undeported...Inertia
3

Inertia Post Displays Query String

Just wondering why post request displays query string? https://prnt.sc/6rcrsqeFNHRH Here is the method for button in my vue mergePrepare() { Inertia.post(route('customers.merge.prepare',{ customers: this.mergeCustomerList })) } My controller /** * @param MergeCustomerRequest $request * @return \Inertia\Response */ public function mergePrepare(MergeCustom

eskiesirius's avatar
eskiesirius's avatar MohamedTam...Inertia
5

Sorting with closure with Eloquent query builder

On the collection, I can do the sorting with a closure like this $collection = collect([ ['name' => 'Desk', 'colors' => ['Black', 'Mahogany']], ['name' => 'Chair', 'colors' => ['Black']], ['name' => 'Bookcase', 'colors' => ['Red', 'Beige', 'Brown']], ]); $sorted = $collection->sortBy(function (array $product, int $key) { return count($prod

anchan42's avatar
anchan42's avatar SnapeyEloquent
7

What loads faster - eloquent query or collection search?

The question is rather rhetorical. But I will definitely meet him soon. Therefore, I want to ask for advice in advance. Example: There is a table of products and they have many-to-many relationships by category, color, brand, etc. I need to get all the products from the Clothing & Sneakers category and also filter the ones that are blue or red, brand adidas. Option 1: Get a

Rediska's avatar
Rediska's avatar RediskaEloquent
4

Mysql query with ordering in mysql is very slow

Hi, I have problem with my sql query. I have table: CREATE TABLE `psUploadedFiles` ( `id_uploaded_files` int(10) UNSIGNED NOT NULL, `enable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `company_id` int(11) NOT NULL DEFAULT '0', `id_user` int(11) NOT NULL DEFAULT '0', `file_path` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `file_name` varchar(75) COLLATE

trifek's avatar
trifek's avatar Tray2General
8

Unique email in my query

Hi! Have a query that returns dupicate users public function getNotificationFlowUsers($golfID) { $flowusers = User::where('golfclub_id', '=',$golfID) ->leftJoin('role_user', 'role_user.user_id', '=', 'users.id') ->where('role_user.role_id',10) ->orWhere('role_user.role_id',1) ->orWhere('role_user.role_id',4) ->o

birdietorerik's avatar
birdietorerik's avatar krisi_gjik...Laravel
3

Query inconsistently works with like on integer column

In my controller, I am using the following query where member_number is a INT MySQl column WorkHours::query() ->when(Request::input('search'), function ($query, $search) { $query->withWhereHas('user', function($query) use ($search) { $query->where('last_name', 'like', "%{$search}%")

wonder95's avatar
wonder95's avatar LaryAILaravel
1

Eloquent query [solved]

I have 3 tables cards card_users users Cards model class public function cardUser() { return $this->belongsTo(CardUser::class); } CardUser model class public function user() { return $this->belongsTo(User::class); } In Card controller and I need make a filter by typing an email and search cards by user accross CardUser model. Car

bashman's avatar
bashman's avatar automicaEloquent
3

Help with Query that has many relationship

Hi, hopefully someone can help. I am trying to get some results. I have a Location table and a Results table. A Location can have many Results which contains a filed called score. Location id name Result id location_id score I am trying to get the Locations with grouped results as a total so that we can see what the best score is between the locations. I can do a SQL query

c-andrews's avatar
c-andrews's avatar c-andrewsLaravel
4

Nested select in query builder.

Hi there, still new to laravel and learning more... got a curious one i cant seem to find an answer for. Currently i have this working: $job_owner = DB::table('TABLE_A')->select('username')->where('job_id',$request->job_id)->limit(1)->get(); $details = DB::table('TABLE_A') ->leftJoin('TABLE_B','TABLE_A.username','=','TABLE_B.username') ->select('TA

curiouspete's avatar
curiouspete's avatar pturulaLaravel
2

How to use wherePivot() inside whereHas() in a query ?

Hello, I have to filter some data via a specific pivot table column. $events = Event::query() ->whereHas('teams', function ($query) use ($user) { $query->whereHas('users', function ($query) { $query->wherePivot() // here the pivot table is team_user and I need to check a column from the pivot table }); }) ->orWhereDoesntHave('teams') ->get();

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
4

Not able to use orderby with distinct in laravel Query

Hi all, I am using Laravel 10. Given below is my Laravel query. $property = DB::table('properties') ->leftjoin('property_sub_types', 'property_sub_types.id', '=', 'properties.property_sub_type') ->leftjoin('property_images', 'properties.id', '=', 'property_images.property_id') ->leftjoin('cities', 'cities.id', '=', 'properties.city')

radhamadhavamhostel's avatar
radhamadhavamhostel's avatar radhamadha...Laravel
7

Laravel single query different output

I have a query like this $query = MyModel::query()->where('status', 1); and I want to get a different output $result1 = $query->where('type', 1)->count(); $result2 = $query->where('type', 2)->count(); $result3 = $query->where('type', 3)->count(); result for $result2 and $result3 is wrong. So I try to get the raw queries from this $result1 = $query->whe

deadrabbits's avatar
deadrabbits's avatar clickLaravel
1