What is FUNCTIONS keyword doing in mysql query?

I have this SQL query. which is a solution to this hackerrank sql problem. SELECT X, Y FROM FUNCTIONS F1 WHERE EXISTS (SELECT * FROM FUNCTIONS F2 WHERE F2.Y = F1.X AND F2.X = F1.Y AND F2.X > F1.X) AND (X != Y) UNION SELECT X, Y FROM FUNCTIONS F1 WHERE X = Y AND ( (SELECT COUNT(*) FROM FUNCTIONS WHERE

rajeshtva's avatar
rajeshtva's avatar kokoshnetaCode Review
9

Pass Query String Parameter to Nested Livewire Component

I need to pass a query string parameter to a livewire component that's nested inside a non-livewire blade / controller. I'm using a Jetstream scaffolding so it's not straightforward to simply convert the parent to livewire. Is there a way to pass query string parameters down to the livewire child component without causing myself too much grief? Thanks.

dpower's avatar
dpower's avatar dpowerLivewire
2

Problem with Laravel SQL Query

I have this Query which i am trying to execute $other = $this->electricityConnections->select('category_id') ->from('building_category_settings') ->where('building_id', '=', 52) ->where('hide_from_electricity_widget', '=', 1) ->groupBy('category_id') ->orderBy('kwh_used', 'desc') ; $electCategory = $th

monstajamss's avatar
monstajamss's avatar SinnbeckLaravel
9

Writing SQL Query in Laravel Eloquent Way

I have this SQL Query "SELECT MIN(IF(c.description IS NOT NULL, c.description, yec.cat_desc)) AS cat_desc, SUM(yec.{$time_period->yec_kwh_used}) AS kwh_used, SUM(yec.{$time_period->yec_cost}) AS cost, yec.category_id FROM `{$time_period->yec_table}` AS yec LEFT JOIN category AS c ON yec.category_id = c.id WHERE yec.category_id <> '$billing_categ

monstajamss's avatar
monstajamss's avatar monstajams...Laravel
2

What will be the query ?

One USER can have multiple LISTINGS and a LISTING can have multiple RESPONSES. I need to calculate total number of responses of listings by a USER. What will be the query ? Models class Listing extends Model { use HasFactory; protected $guarded = []; public function messages(){ return $this->hasMany(Message::class,'listing_id','id'); } public

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
5

Change Vanilla SQL Query to Laravel

I have this query in my Vanilla PHP and SQL I do i write this statement in laravel? $sum_data = App::sql()->query_row("SELECT SUM({$time_period->yec_kwh_used}) AS kwh_used, SUM({$time_period->yec_cost}) AS cost FROM `{$time_period->yec_table}` WHERE category_id = ".sample::CATEGORY_BILLING." AND building_id = $building->id"); $total_kwh = $s

monstajamss's avatar
monstajamss's avatar SinnbeckLaravel
3

What is the correct query in my case with pivot table?

I'm having hard time figuring out the query I need in my scenario. Not sure I can use eloquent alone and I might use raw query. There are the following tables: lists, items, and the pivot table items_to_lists, which has the columns list_id and items_id Relationships are set like so - items hasMany items_to_lists and lists belongsToMany items_to_lists I am given a list id, and I

cooperino's avatar
cooperino's avatar cooperinoCode Review
8

How to detect where exactly is the line of code that is executing a specific query through clockwork

Hey everyone! I notice queries that doesn't actually exist in the controller on the page I'm visiting but there is nothing to show which exact line is the code that is executing the query. Can you guys show me how can I determine the line of code for each query executed using clockwork?

lat4732's avatar
lat4732's avatar LaralexLaravel
3

How to use Yajra DataTables with raw query and not model?

The docs show that you need a Model that corresponds to the DataTable: public function query(User $model) { return $model->newQuery(); } But what if I have some more complex query that is not a Model? For example: return $data = Auth::user()->select(//...) ->where(//...) ->where(\DB::raw(//...)) ->leftJoin(//...)

cooperino's avatar
cooperino's avatar cooperinoCode Review
4

laravel query room available

Dear All, According to I have 2 tables and I would like to show available room on system so I have command below for example So My question is How can I create it as eloquent for query these? SELECT r.room_id FROM rooms r WHERE r.room_id NOT IN ( SELECT b.room_id FROM bookings b WHERE NOT (b.end_datetime < '2012-09-14T18:00' OR b.start

nitiphone's avatar
nitiphone's avatar nitiphoneEloquent
2

Need some help with a query and a chart

Hey everyone! I have a graph that is separated by months for a period of 1 year. I'm displaying how many reviews a website received each month. Here's my query: $website->loadCount([ 'reviews as firstMonth' => function ($query) { $query->whereBetween('created_at', [Carbon::now()->subMonths(12), Carbon::now()->subMonths(11)]); }, 'reviews as se

lat4732's avatar
lat4732's avatar lat4732Laravel
0

query Not filtering by date

Hi all Need to show all vehicles witch are free according to the date but filtering by date is not working my code is $query->where(function ($query) use ($end_datetime, $start_datetime) { return $query ->whereDoesntHave('vehicle_rental_times') ->orWhereHas('vehicle_rental_times', function

KalimeroMK's avatar
KalimeroMK's avatar MohamedTam...Laravel
2

Laravel Spatie Media query

Hello, i'm working with Laravel Spatie media library. when i search the media using a simple where() eloquent query, i get an empty collection, i tried using id it worked, i found a workaround which is: $mediaEmpty = $product->getMedia()->isEmpty(); // i had to convert it again to sql $sizeEmpty = $product->getMedia()->toQuery()->where('file_name', 'like', '%800

Benounnas Oussama's avatar
Benounnas Oussama's avatar Benounnas...Laravel
1

how to change query with function on DB::table?

Hi, I have function create function imam_id() returns INTEGER DETERMINISTIC NO SQL return @imam_id and view CREATE VIEW data_kitab AS SELECT a.id id, a.id_imam, a.id_kitab, a.kitab_indonesia, a.kitab_arabic, b.total_bab, c.total_hadits, c.min_row, c.max_row FROM hadits_kitab a INNER JOIN ( SELECT a.id_kitab, COUNT(1) total_bab FROM hadits_kitab a INNER JOIN hadits_bab b ON a.id

kubiga.cr7's avatar
kubiga.cr7's avatar kubiga.cr7Eloquent
0

how to query where inside json column in laravel elequent

i have a model called orders which have an info json column . in that json i have a property called id . now i want to retrieve all orders with the id of 6 for example . so here is how i have done like below : $order = \DB::table('orders') ->where('info.id',$value) // ->where('info.id',$value) // ->whereRaw('JSON_EXTRACT(`inf

farshadf's avatar
farshadf's avatar MohamedTam...Laravel
3

How to handle search page query ??

On then search page I have few filters : The controller is : $brokers = User::whereRoleIs('broker') ->where('city', $city) ->where('locality', $locality) ->where('plots', $plots) ->where('apartments', $apartments) ->where('agricultureland', $agricultureland) ->where('internationalp

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
10

How to execute Cross join using Raw Query?

I have an SQL query that I want to convert to Laravel DB Query. But, I am unsure how to use Raw Query for Cross Joins. SELECT DISTINCT json_t.id FROM my_table, JSON_TABLE(my_table.array_ids, "$[*]" COLUMNS(id char(36) PATH "$")) AS json_t WHERE my_table.array_ids IS NOT NULL Can someone please help me to convert this query into Laravel DB Query?

SarjitDelivala's avatar
SarjitDelivala's avatar SinnbeckEloquent
1

Query by relations and problem with whereHas speed

I have models Product, Property, PropertyValue and product_property_value intermediate table for Many to Many relation with structure: product_id property_id When products fiters are in action, filter generate query builder like this Product::query() ->whereHas('propertyValues', function ($query) { $query->where('property_id', 'some_property_i

jordan-dev's avatar
jordan-dev's avatar jordan-devEloquent
2

How do I highlight the matched letter/word from a search query inside a text

Hey, @everyone! How do I highlight the matched letter/word inside a text when I run a search query? Here is an example. How can I do this? Is this some kind of jQuery or what? Maybe I can get the value from the search input and look through the DOM for a word or letter inside a specific div. Is that a option?

lat4732's avatar
lat4732's avatar LaralexCode Review
2

Equivalent between Eloquent and pure MySQL query

Hello, I have an eloquent query which don't really work fine. And I have the pure MySQL query which works as I want. Even I have a distinct() method, for the product which have 2 units, only one unit is shown. $products = DB::table('products') ->join('ingredients', 'ingredients.product_id', '=', 'products.id') ->join('units', 'units.id', '=', 'ingredients.unit_id'

vincent15000's avatar
vincent15000's avatar vincent150...Eloquent
10

formatting date from a a query as 25 Mar, 2022

How do i correctly format date fromat query? format 25 Mar, 2022 $group = DB::table('appointments') ->Where('doctor_id', $user) ->select(DB::raw('DATE(date) as date'), DB::raw('count(date) as count')) ->groupBy('date')->format('%d-%m-%Y') ->get();

tomasosho's avatar
tomasosho's avatar jlrdwCode Review
12

What is best way to shorten my eloquent query?

I really need help with this ugly, long eloquent query I have written, which includes too many WHERE clauses. At this point, to be honest, I'm so confused that I don't even know what this code suppose to do! But I try my best to describe it as clear as possible! There are two tables named Users and Contacts. Every user can send contact request to other users AND "SEND Requ

revilcv's avatar
revilcv's avatar revilcvEloquent
3

Refactor a part of a query to a Trait

I have this query: $cruds = Crud::inTeamActive()->with([ 'color' => function ($q) { $q->where('deleted', 0); }->get(); I use the with() over and over in many controllers, so I like to refactor it to a Trait since is always the same where() statement attached. Sometimes I have multiple withs, with(['color', 'size', ...]) eac

muuucho's avatar
muuucho's avatar muuuchoLaravel
3

Laravel product query filter

Hi all, I am looking to build a query that would filter my products based on some input checkbox selectors. $courses = Course::with('level', 'campus')->where('university_id', $university->id)->where('name', 'LIKE', '%'.$request->search.'%')->whereIn('levels', $request->levels)->paginate(10); it works just fain when $request->levels is set but would fail

tnort's avatar
tnort's avatar tudosmEloquent
2

Convert into query builder ?

I have a raw query like this. DB::select("select currency_id, sum(total) as total from (select sum(amount) as total, user_package_id, currency_id, user_packages.created_at from user_package_payments inner join user_packages on user_packages.id = user_package_payments.user_package_id group by user_package_id,currency_id having sum(cas

somenet77's avatar
somenet77's avatar rodrigo.pe...Eloquent
7

Redirect URL with query parameter to another URL

i have a URL with query parameter like below, https://www.example.com/blog?cat=20 I want to redirect above URL to https://www.example.com/blog htaccess code is not working Any help?

Deekshith's avatar
Deekshith's avatar DeekshithGeneral
2

->with() do not join but do another query

Hello, My Debugbar displayed a lot of duplicated queries. So I tried to investigate. I would like to retrieve all the documents belonging to a user as well as all the authors of these documents (with their avatar). Here is the code : $documents = $user->documents()->with('user.avatar')->get() I thought that it would use "INNER JOIN" to select all that elemen

mallaury's avatar
mallaury's avatar SinnbeckEloquent
5

Laravel debugbar memory exhaustion due to large SQL query

I have a route which produces a job batch to import large dataset (approx 100,000 entries) chunked in 1000 entries per job. This produces a large SQL query to produce the batch. The issue is that this large SQL query is parsed by debugbar package which maxes out the memory limit in the QueryCollector data collector. The specific line which causes the error is at C:\...\vendor\b

CLab's avatar
CLab's avatar CLabGeneral
2

I need some help with a count query

Hey @everyone! I need to count reviews for the past 12 months grouped by months. First thing that appear in my head is to assigning values to 12 different variables. The second thing appeared in my head is that I can use loadCount for it: ->loadCount([ 'reviews as firstMonth' => function ($query) { $query->whereBetween('created_at', [Carbon::now()->subMonths(11)

lat4732's avatar
lat4732's avatar SinnbeckLaravel
2

Define route in Laravel with multi parameters for multi conditions in query

i have a table user in database and i want to define a route to query users in table with different "where" conditions passed by url. Example i want to query users with only 2 conditions B and C but sometimes i want to query users with 3 or more conditions like B, C and D .... How should i define a route or some routes to solve this problem. Thank you for reading!

pthai-it-dev's avatar
pthai-it-dev's avatar pthai-it-d...Laravel
2

Optimise a query working out average duration in seconds

I have this query: $avglisteningtime = TrackTimes::query()->where('user_id', '=', $user->id)->avg('time_listened'); And this is getting the average listening time for all tracks owned by a user, of which there may be thousands of lines. The TrackTimes model looks like this: ID: 7 CREATED: 2018-11-24 23:10:22 UPDATED: 2018-11-24 23:10:22 TRACK ID: 4 TIME LISTENED: 26

Mick79's avatar
Mick79's avatar sr57Eloquent
6

Elequent relationship query

I have Item that hasMany statuses .. I want to create a method inside the item model to know if it has a status 'Review' or not I came up with this but no hope. public function scopeReviewed($query) { return $this->whereHas('statuses',function(Builder $query){ $query->where('status','Review'); })->count() > 0; } any help?

lifesound's avatar
lifesound's avatar ehab.abosh...Eloquent
1

query in relationship

i have Category hasManyThrough relation on OrderItem Model OrderITem belongs to Order Model i want to query on order relationship Category Model public function orderItems() { return $this->hasManyThrough(OrderItem::class, Product::class); } OrderItem Model public function order() { return $this->belongsTo(Order::class); } my eloq

freemium's avatar
freemium's avatar SilenceBri...Eloquent
3

Help with nested relationship query.

Equipment has many users. Users attach to one employee. This query is returning 'active' for the equipment, not the employee. I need only active employees to show, but still return the equipment info if none exists. return Equipment::where('slug', $slug) ->with(['users' => function ($query) { $query->with(['employee' => function ($

ajsmith_codes's avatar
ajsmith_codes's avatar tykusLaravel
5

defining 2nd database connection inside a query

so i have this query where i have to take joins between two tables in different databases Works fine for me on one server but not on another where it gives me a connection error so i'm trying to explicitly define connection inside the query is there any way i can do that? $document_changes = DocumentChange::select([ "document_changes.id", "document_c

Haseeb69's avatar
Haseeb69's avatar SinnbeckGeneral
7

Help about eloquent query

Hello guys, I have 3 tables projects, employees, employee_logs Each employee work just on one project and each project has many employees (one to many), in employee_logs we store time that each employee working on project ( date, start_time,end_time, employee_id) so between employee and employee_logs we have one to many relationship. Employee table fields(id,name, email,tel,pro

Ehsan Bagherzadegan's avatar
Ehsan Bagherzadegan's avatar Ehsan Bagh...Eloquent
2

Need help with totaling column in eloquent query.

I've successfully imported all time clock punches of our employees. I'm trying to get a list of the punches grouped under the employee. I want to total the column 'total_time_converted' before sending it to the view. I tried these two queries and had no luck. Error for this one: "call to undefined method Illuminate\Database\Eloquent\Builder::mapInto()" $quer

ajsmith_codes's avatar
ajsmith_codes's avatar SinnbeckLaravel
20

Is it possible to return a parsed json column using graphql query?

The graphql query returns a string when I use JSON type in graphql.shema. Is it possible to return a parsed json? Your help is much appreciated!

hellomohsinhello's avatar
hellomohsinhello's avatar hellomohsi...Code Review
0

SQL to Query Builder

I have an sql statement that I am struggling to convert to a Query Builder statement: SELECT m1.* FROM chat m1 LEFT JOIN chat m2 ON (m1.chat_id = m2.chat_id AND m1.id < m2.id) WHERE (m1.from_id = 4 or m1.to_id=4) AND m2.id IS NULL Any help is appreciated!

TheoR's avatar
TheoR's avatar TheoREloquent
9

How to check if a collection from a collection is empty (Query Builder?)

I am trying to see if $buyer->membership_number has a value or if it is empty. $buyer being a collection. If I dd($buyer->membership_number) I get another collection, named Value. How do I check what/if the second collection returns what I want or not? I can't use isEmpty() because it is the query builder that I am using, if I understand it correctly. if($buyerIndex ===

Trocamedo's avatar
Trocamedo's avatar TrocamedoLaravel
6

Query works from Request but not from Policy

In my Custom Request authorize I have the following query: ItemsList::query()->whereIn('id', $lists) ->where('account_id', '!=', $this->user()->account_id)->doesntExist(); but when I try to pass the parameters to a Policy instead, it always returns false. This is how I pass it from the authorize method to the Policy: // in the Request file public fun

cooperino's avatar
cooperino's avatar cooperinoCode Review
6

Simple query returns empty array no matter what

I have a simple query: ItemsList::query()->where('account_id', '==', $this->user()->account_id)->get(); But I always get Illuminate\Database\Eloquent\Collection {#1952 #items: [] #escapeWhenCastingToString: false } I checked and $this->user()->account_id returns the correct id, and in the table that corresponds to the model itemList there is definitely a

cooperino's avatar
cooperino's avatar SinnbeckCode Review
5

How to get current executing Raw Query in Model Observer Methods in Laravel?

I'm making a approval system where someone initiates a request to either save/update/delete some data from database but they need to get approvals from specified persons. I've created all the models/jobs/notifications but there were some complexities to do this. I came up with an idea to store requested raw query in database but I couldn't figure out how to get only specific qu

SalmanMalik's avatar
SalmanMalik's avatar SnapeyLaravel
1

Query by parent distinct column and order parent results by child column

Purpose a) The query should return only the latest distinct parent column 'thread_id'. b) The results of the parent should be sorted by child table column read Code EmailMessage::whereHas('messageInfos', function($query) { $query->where('user_id', Auth::id())->where('starred', true); }) ->with(['messageInfos' => function($qu

khanimranm's avatar
khanimranm's avatar khanimranmEloquent
0

Query builder with resource and sum

HI guys im trying to sort data based on sum with query builder my resource is like this public function toArray($request) { return [ // 'id'=>$this->id, 'name'=>$this->name, 'url'=>$this->url, 'image_path'=>$this->image_path, // 'orders' => $this->orders, 'weekly'=&

kaiserkais's avatar
kaiserkais's avatar drewdanLaravel
2

What is the correct query for nested relationships ?

I need to count the total number of PROJECTS by a BUILDER in CITIES ( with status = 1 ). My controller : $topbuilders = Builders::whereHas('project.city', function ($query) { $query->where('status', 1); })->withCount('project')->orderByDesc('project_count')->limit(10)->get();

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

Laravel Eloquent Relationship Like Query

I am trying to query categories where product tags is equal to user selected tags in filter bar , Here is the query what I have tried so far : ​ $categories = Category::with('products'); // selected tags is array which contain tags if (! empty($selectedTags)) { foreach ($categories->get() as $category) { $categories = $category->whereHas('products', function

khizerdev's avatar
khizerdev's avatar SinnbeckEloquent
7

Add data to the same collection without it appearing in the query

Hi, I have the next query $products = collect(); $product = ProductFlat::leftJoin('product_images', 'product_flat.product_id', '=', 'product_images.product_id') ->select('product_flat.product_id as id','product_flat.name', 'product_flat.url_key as slug', 'product_images.path as image','product_flat.price') ->addSelect(DB::ra

qTylok's avatar
qTylok's avatar tykusLaravel
7

What is the best way to share a query for global use in all views for a controller?

Hello everyone! I need to pass the query below from my controller to the included footer (in every page) without defining it in each controller method. I've tried with defining a boot method in my controller like this: public function boot() { $footerCategories = Categories::where("is_top", 1)->orderBy('created_at', 'desc')->limit(5)->get(); v

lat4732's avatar
lat4732's avatar 01101100 0...Laravel
7

check for successul elequent query

i do not know why if conditional always yields an error public function step2(Request $request) { $invoice_id = session('invoice_id'); $invoiceWithItems = Invoice::with('items')->find($invoice_id); if($invoiceWithItems){ return view('components.pages.guest.step2page', ['invoiceWithItems' => $invoiceWithItems]);

lifesound's avatar
lifesound's avatar tykusEloquent
17