Is there a better way to pass an eager loaded query as a subquery to another query?

I'm trying to do a quite complicated query (vastly simplified for the examples) while still keeping things inside eloquent rather than raw SQL. The query grabs the max value by created_at that is unique to a specific relationship bin_id. Fortunately, T-SQL (SQL Server) provides a function row_number() that makes this dead simple. Unfortunately, it requires being inside a CTE or

ryanmortier's avatar
ryanmortier's avatar ryanmortie...Eloquent
0

Execute SQL query taken from Table with Laravel DB Raw

I have a project build with Laravel 8. My project will use for creating dynamic report that run with SQL query. So i must save the SQL query in my database to run dynamic every report. It's all fine since i faced problem if my query are dynamic. my code on Controller: public function data(){ $id = 1092 $var = DB::table('abc')->where('id', $id)->first(); $query =

hafidm93's avatar
hafidm93's avatar Tray2Laravel
2

Strange query on updateOrCreate

Hey guys, im using updateOrCreate to update a pivot table with a mass of entries. The queries that will be executed are wrong and i have no idea where it comes from. Maybe some of you have an idea. The pivot table looks like: customer_id store_id role_id last_populate_at I have an unique index on the three columns customer_id, store_id, role_id (customer_store_customer_id_sto

webcodecs's avatar
webcodecs's avatar webcodecsEloquent
8

Easy query that should not give null

$product = Product::where('serial', $serial)->orWhere('mfg_serial', $serial)->first(); why this query yields null where I have the serial and I am sure of that

lifesound's avatar
lifesound's avatar lifesoundEloquent
8

Help with MIN in query

I am trying to figure out how to get the records with the lowest value in a db column in a join query. Part of the query is // GET THE DEFAULT IMAGE IN THE JOIN TABLE ->join('gallery_photos as gp', function ($join) { $join->on('gp.gallery_id', '=', 'gallery.id') ->orderBy('gp.sort', 'asc') ->where('gp.sort', '=', 1); }) // GET TH

movepixels's avatar
movepixels's avatar LumethysLaravel
9

Paginate a query

Looking for some help with a paginated query sort. I am able to get the correct records, but I can't seem to order them properly. $records = Profile::select(['id', 'name', 'headline', 'entertainer', 'dob', 'slug']) ->where('status', 100) ->whereHas('User', function($q) { $q->where('banned', false); }) ->whereHas('Subscription', functi

movepixels's avatar
movepixels's avatar LoverToHel...Laravel
7

Continuous API query - How to implement?

The API is very special and I will try to explain it with a simple example. Each user can enter several personal tokens into the database. For each of these tokens, I would like to call an API every 15 minutes (later perhaps more often) and store the response in a database. The API is a Rest API that I could query with e.g. Guzzle. Since I haven't been working with Laravel for

KodaC's avatar
KodaC's avatar KodaCLaravel
4

request() query parameter inside anoynmous component?

When i try to access query parameter with @php @endphp in my anonymous component with request()->input('parameter') or request()->query('parameter'), i get alyway null. It is possible to access it with facade or helper, without to pass it to the component itself? My approach is to access on a ?back=my_path for a back Button. My problem is, when i open a livewire componen

robertp1980's avatar
robertp1980's avatar automicaLaravel
1

How to query to match all in array?

Hi, I'm having some trouble getting my eloquent query right. Let's say I have an array of ID's: $features_array = [1, 4, 6] Using a whereIn() as below, I've build a query that returns items that match any ID in the $features_array. But how would you get only the items that have all features in the array? Items::select('*') ->when($features, function ($query, $features)

tvbz's avatar
tvbz's avatar MichalOrav...Eloquent
2

Query with multiple withCounts

Hi, I am trying to figure out how to get a relationship grouped based on different criteria. I figured out that I could potentially have multiple counts that address different criteria but that doesn't seem to be the best approach. MyQuery ->when($includeTests, function ($query){ return $query->withCount(['englishTestResults as passed_tests' => function

tnort's avatar
tnort's avatar tudosmEloquent
4

Struggling with a query that subtracts sum of relationship column from another relationship column if that relationship exists

I have Product, ProductStock and CartProducts models. I have been trying to figure out how to build a query which: Gets all products where product_stock.stock >= 1 , where product_stock.stock - sum(cart_products.count) > 0 (cart_products where cart_products .product_code = products.code) or where doesn't have any cart_products relation, but still has product_stock.stoc

r123's avatar
r123's avatar AungHtetPa...Eloquent
1

Extract field names from a DB::select() query

I'm working on a dynamic CSV generator and I'd like to access the field names I've assigned in my query. I realize I can access a table's column names with Schema::getColumnListing(). But my custom query results won't use table column names. For instance, if my query looks like this: SELECT id as customer_id, name as customer_name FROM customers I need to dynamically

dpower's avatar
dpower's avatar dpowerGeneral
5

Mysql query

I have tables called categories fields(id, name), category_product fields(id, product_id, category_id), and products fields(id, name, etc). Assume, that product 1 has 2 categories. I want to query categories related to product 1 or 2 and so on. How can I write the join query? Thank you

feralam's avatar
feralam's avatar feralamGeneral
4

Prevent dublicate query

Hey! I have this in my AppServiceProvider Blade::if('hasCompany', function () { return auth()->check() && Companies::where('user_id', auth()->user()->id)->exists(); }); Blade::if('missingCompany', function () { return auth()->check() && Companies::where('user_id', auth()->user()->id)->doesntExist(); }); But everytime I use @hasCompany o

lat4732's avatar
lat4732's avatar NiushLaravel
2

Query runs raw but not with Query Builder `->get()`

Hi there! I'm struggling for a while already with the following problem: I generate this instruction with the Query Builder (code below is the result of a DB::getQueryLog()), but it doesn't retrieve the data, with no errors, only an empty array. array:1 [ 0 => array:3 [ "query" => "select `id`, `content` from `mediator_responses` where (`private` = ?

_camilo_'s avatar
_camilo_'s avatar SinnbeckEloquent
7

sub queries and query builder

is there a way to write a sub query and use query builder (needed to add 'if' clauses not included below for simplicity) Here is the query I am trying to build in query builder: select license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, derivedtable.weightvaluesum, derivedtable.plantcountavg, (derivedtable.weightvaluesum/derivedtable.plantcountavg

scottsuhy's avatar
scottsuhy's avatar scottsuhyLaravel
6

Why does my laravel job fails with "No query results for model"?

I have a batch that calculates a score for many models. There is a horizon worker that process the batch, and most of the times, it runs with success. However, sometimes, a job CalculateFieldScores fails with the message No query results for model [Mz\Models\Ipa\Category]. The Category table in the database haven't change for more than a year, so it should always exist. Here

naghal's avatar
naghal's avatar naghalLaravel
2

login is creating a query string

My issue, if i am on a page like https://example.com/invoices and i have been logged out then after logging in the url is now https://example.com/invoices?%2Finvoices= Is this a known issue? is there a way to prevent this query string and/or fix it? I did post a question on this on stackoverflow several months ago but nobody had any relevant ideas. My app is due to go onto a pr

CraigStanfield's avatar
CraigStanfield's avatar CraigStanf...Laravel
2

I want SQL To Laravel Query Builder

hello. i want change SQL to Query Builder SELECT c.*, (SELECT msg FROM chat_msg WHERE chatcon_id = c.id ORDER BY submit_date DESC LIMIT 1) AS msg, (SELECT submit_date FROM chat_msg WHERE chatcon_id = c.id ORDER BY submit_date DESC LIMIT 1) AS msg_date, a.name AS account_sender_full_name, a2.name AS account_receiver_full_name FROM chat_con c JOIN users a ON a.id = c.account_s

wallock's avatar
wallock's avatar SinnbeckLaravel
7

sql query last_messages displays wrong messages

Hello, i have query like this, but displays wrong message. $users = Message::join('users', function ($join) { $join->on('messages.from_id', '=', 'users.id') ->orOn('messages.to_id', '=', 'users.id'); }) ->where(function ($q) { $q->where('messages.from_id', auth()->user()->id)

boboboy's avatar
boboboy's avatar Tray2Code Review
5

Optimising Eloquent Query with Relationship

I have ~500 users whom each have a new metrics row added to the DB each day. I was running a query to find the difference between a value from their first entry and their last. I decided to eager load the relationship to cut down on DB calls. Something like... $users = User::with('metrics')->get(); foreach ($users as $user) { $difference = $user->metrics->last()->

brandymedia's avatar
brandymedia's avatar SinnbeckEloquent
6

How to filter a field using json_decode function in Laravel Query?

I want to filter child records with conditions if an element is present like in_array(someElement, json_decode(database_column)) but don't know exactly what will be the syntax. I have done it through loops but I want to clear that loops and wrap it up in one query. Till now I have done the following, $projects = Project::with([ 'tasks' => function($query) use ($request)

Engr khayam khan's avatar
Engr khayam khan's avatar MohamedTam...Eloquent
3

How to query between two ranges of time

How do you query between two ranges of time without including the two ranges themselves? I have a working code of querying time using whereBetween but I'm having an issue because I only want to get the records between those two ranges of time, not including themselves. I have these rows on my schedules table: +----------+------------------+----------------+-------------+ | i

NoLAstNamE's avatar
NoLAstNamE's avatar benjamin15...Eloquent
14

method on a Model that returns scoped query results

How can I make a method on a model to return a query results as instead of query Builder instance as in the case of query scopes? Let's say I have a model that has numerous columns with username and password among them. So currently to get the username and password array from the model I have to do this: MyModel::credentials($userId)->first(['username', 'password'])->toAr

dmag's avatar
dmag's avatar dmagEloquent
3

How to make foreach with query better?

Hello! In my code I get a products list with products quantity to send for orders and some complaints. This code is not good because I use query in foreach. How I can do this better? class GetProductsDestinationsList { public function __invoke() { $orderProducts = OrderProduct::where('status', OrderProductStatusNew::$name) ->select('product_id', '

Sinres's avatar
Sinres's avatar gtyEloquent
1

Dynamic model declaration VS using Query Builder

I want to get the model or table name from the request and then update some values on some rows in that table. I can either do it dynamically with Eloquent: $model_name = $request->model_name; $namespaced_model = "\App\Models\" . $model_name; $instance = $namedspaced_model::find($id); $instance->value1 = "new_value1"; $instance->value2 = "new_v

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
6

Export data after its being filtered using query strings

Hi, I have a page where I can filter data using different filters via query strings. What I want is to have a button and get to export already filtered data. I have tried to use an additional query parameter to indicate when I want to export which is set to null by default but when I click the export button it sets the query parameter to 1 which would trigger the export on the

tnort's avatar
tnort's avatar tudosmGeneral
1

Laravel 9 - Error message output on filter query due to relation

Hello, i'm having a little problem by filtering results. Controller: try { $data_details = OrderDetail::where('order_id', $id) ->when($filters['filter_by_code'], function ($data_details, $code) { $data_details->where('codice_articolo', 'like', '%' . $code . '%')->firstOrFail(); }) ->

shaddark's avatar
shaddark's avatar Shivamyada...Laravel
5

Should I validate all query params GET request

Should I validate all query params GET request. And if yes, when should I do that? Thank you!

pthai-it-dev's avatar
pthai-it-dev's avatar kokoshnetaRequests
7

How to join more than one table while performing search query in Laravel?

I'm trying to perform a search that involves 3 tables. Please help me to achieve this. Code from the controller. Thank you in advance. public function index(Request $request) { $linked = Linked::paginate(10); // Table-1 $customers = Customer::all(); // Table-2 $item = Item::all(); // Table-3 $query = $request->search; if($query){ DB:

Syed1980's avatar
Syed1980's avatar Syed1980Laravel
12

Job Queues and Query Scopes

I have a fairly mature Laravel app that helps me manage my business. Recently I refactored the codebase to allow for multiple: projects and users. //models/Project public function users(){ return $this->belongsToMany(User::Class);//pivot table relationship } //models/User public function projects(){ return $this->belongsToMany(Project::Class);//pivot table rela

bionary's avatar
bionary's avatar JaanLaravel
6

Query doesn't work inside Laravel

The Eloquent query: $users = Message::join('users', function ($join) { $join->on('ch_messages.from_id', '=', 'users.id') ->orOn('ch_messages.to_id', '=', 'users.id'); }) ->where(function ($q) { $q->where('ch_messages.from_id', Auth::user()->id) ->orWhere('ch_messages.to_id', Auth::user()->id); }) ->where('users.id','!=',Auth::user()->id) ->select(

imawesome's avatar
imawesome's avatar LumethysEloquent
4

Help optimizing 25mb query

Hi guys. I'm using a data table to list, search and filter my users. I'm getting a 25mb query. First problem is I don't know how to reduce the records and still be able to filter them from the data table so I'm getting all the users. The second problem is that I need the last login from Spatie Activity log, that runs more queries which leaves me with 1756 queries, 1464 models f

kylemabaso's avatar
kylemabaso's avatar dysentry30Laravel
3

How to parameterized a query using LIKE

I am having an issue with the syntax for parameterized a query using LIKE. This works $data = DB::table('users') ->select("users.id", "users.firstname", "users.lastname", "users.email" ) ->whereNotIn('users.id', $usersEnrolle

ctyler's avatar
ctyler's avatar rodrigo.pe...Eloquent
6

Laravel Queue Job - No query results for model (After Commit = true)

Hey community, recently, sometimes jobs (SerializedModels) failed with the Exception: Illuminate\Database\Eloquent\ModelNotFoundException: No query results for model [..] Controller: $model = new Model(); $model->save(); // Log::warning("Before dispatching", $model->toArray()); // Model displayed correctly, (id set) Job::dispatch($model); // Connection: Redi

dash222's avatar
dash222's avatar ander7agarLaravel
10

how to query with relation's 'having count greater than' condition

I have 3 models. User / Post / Category Following is my model code, models are simple. // User class class User extends Model { public function posts(){ return $this->hasMany( Post::class); } } // Post class class Post extends Model { $fillable = ['reg_date', ...]; function user(){ return $this->belongsTo( User::class); } function

binggle's avatar
binggle's avatar binggleEloquent
2

How to query 'dob' date with datatype timestamp without time zone for age between from and to ages?

Dear Friends, I have a search form with 'age from' and 'age to' dropdown it's value range from 1-100 years. I need to search the query in a table with field 'dob' with datatype 'timestamp without time zone'. As a newbie I couldn't estimate how it can do ? any body can advise how it can do . Waiting your fast reply Thanks Anes P A

insight's avatar
insight's avatar insightLaravel
2

How to pass additional Closure to query scopes?

Hey guys, Let's say I have Products, a Category tree with categories and descendants, and a scope: Product::ofCategoryAndDescendats(1)->get(); This scope gets all Products that are related to Category 1 and all its child categories. In some parts in my code I'd like to put constraints on those child categories. Get all Products in Category with id 1 and in all categories th

1000ml's avatar
1000ml's avatar kokoshnetaEloquent
6

Convert SQL query to Laravel QUery Builder

Please can someone help me convert the query below to a LAravel query builder "select `products`.*, (select count(*) from `order_items` where `products`.`id` = `order_items`.`product_id`) as `order_items_count` from `products` where `products`.`deleted_at` is null order by `order_items_count` desc limit 8 ◀"

CItyTrader's avatar
CItyTrader's avatar SinnbeckEloquent
6

how to insert Multidimensional Arrays with query builder larave

hi, i have value array like this : ^ array:2 [▼ 3 => array:2 [▼ "id" => "1" "name" => "<span>tester1</span>" ] 8 => array:2 [▼ "id" => "2" "name" => "<span>tester2</span>" ] ] how to insert database with query builder in controller l

vand's avatar
vand's avatar vandLaravel
2

Simple database query takes forever

Hello, my app is connected to oracle database. everything was fine until suddenly every query start to take ages to run. even simplest queries. for example select sysdate from dual this query takes 0 seconds when I run it in sqldeveloper but this route takes about 10 seconds Route::get('test', function () { $date = DB::select('select sysdate from dual'); return $date

uniqueginun's avatar
uniqueginun's avatar Tray2Laravel
19

Query relationship absence after specific date

I have a users table and a activity_log table. When a user logs in/out, a record is created in the activity_log table. How can I query users that have had no activity after a certain date? I know I can do: $absent_users = User::whereDoesntHave('activities')->get(); How can I expand on this to say "return users with no activity logs after 2022-10-01", for example?

jacobcollinsdev's avatar
jacobcollinsdev's avatar jacobcolli...Eloquent
4

Additional query on belongsTo relation when foreign key is null

Let's imagine that we have a product model which belongs to a category, but the category can be null. Product->with('category')->find(1); This gives me a product with a category. If category_id is null, the relationship is also null (as expected). But Laravel runs 2 queries: select * from `products` where `products`.`id` = 1 limit 1 select * from categories where 0 = 1

fireball70's avatar
fireball70's avatar MohamedTam...Eloquent
1

Laravel 9 -query filter data and ignore where condition if null

Hello, i'm trying to filter the results of a table. My form in index.blade <form action="" method="GET"> @csrf @method('GET') <input class="" type="text" name="order_id" id="order_id" value="" placeholder="ID"> <input class=""

shaddark's avatar
shaddark's avatar shaddarkLaravel
2

How to insert all array value to DB with query builder laravel

hello, i have array with value like this : ^ array:3 [▼ 1 => array:2 [▼ "number" => "123" "desc" => "test1 ] 6 => array:2 [▼ "number" => "987" "desc" => "test2" ] 9 => array:2 [▼ "number" => "123" "desc" => "test3"

vand's avatar
vand's avatar aosdevLaravel
11

Eloquent query issue ....

I am developing a real estate site. It has the concept of INNER CIRCLES which has a user as an ADMIN and users can join different CIRCLES. I need to show total number of users ( including ADMIN ) in a CIRCLE , the total number of LISTINGS by all the CIRCLE members and total number of LISTINGS by all the members which are CLOSED. Controller : public function MemberInnerCircles

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
12

SQL find Duplicate Query

I am trying to find duplicates in my SQL table with this query SELECT a.* FROM transactions a JOIN (SELECT user_id, details, COUNT(*) FROM transactions GROUP BY user_id, details HAVING count(*) > 1 ) b ON a.user_id= b.user_id AND a.details= b.details ORDER BY a.user_id The problem is I am trying to narrow it down with a WHERE condition and I just could not get my way around

Agbaje's avatar
Agbaje's avatar SinnbeckGuides
8

Use slug as query for API route

Hi, I'm trying to use a slug as a query for an API route, it's not working and returning a 404, but I'm unsure what I'm doing wrong. Route::get('categories/{category:slug}', [CategoryController::class, 'show']); public function show(Category $category) { return Category::find($category); }

darrylmorley's avatar
darrylmorley's avatar SinnbeckLaravel
5

How To Group Query Results By Category and Display The Cat. in Select2

I have data in a MySQL table that has a name, value and group field. The following is a screenshot of the data table. https://www.screencast.com/t/777cLTbhHC I want to output the table into a Select2 select field so that it looks like the code below where the optgrpup label equals the group name; and the options names and values equal the data from the table rows. <select c

phayes0289's avatar
phayes0289's avatar phayes0289Laravel
2

Laravel SQL query error

Hello. I have error in my Laravel sql query. How can I fix this? $collection ->groupBy('panichd_tickets.id') ->select($a_select) ->with('creator') ->with('agent') ->with('owner.department.ancestor') ->withCount('allAttachments') ->withCount(['comments' => function ($query

huseyinalperenn's avatar
huseyinalperenn's avatar huseyinalp...Eloquent
2