How to write a Nested query in eloquent

I am developing a real estate portal. The relationships are like this City has many localities City has many buildings Building has many listings Building has one builder How to show buildings of only enabled cities. What will be my eloquent query in the controller ? How to show builders of only enabled cities. How to show listings of only enabled cities. Can I code something i

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
4

Speed up relationships query on more than one million record

Hi, I have three columns [ stores - rating_average - ratings ], if a user rated a store a new record will be added to the ratings table [ store_id -rating - guest_id - timestamps ] after that it will count all the ratings for that store id and crate or update the rating_average [ store_id - average - ratersCount ]. Now when the user click and view a listing (store) I make an aj

ibrahimwithi's avatar
ibrahimwithi's avatar ibrahimwit...Eloquent
15

Best practice on retrieving analysis data from multiple query?

I have to run multiple query to get different output and show to the user. For example, total_user, total_sale, average_engagement, total_product, and etc. I have to filter the data and do comparison maybe in daily, weekly, monthly and yearly. I am thinking which is the suitable way to get it done. I would like to create an analysis table to store these data, so i will have dai

Jeffxy's avatar
Jeffxy's avatar Tray2Feedback
4

Eloquent conditional query based on database-field

Hi, I have a table like this: +----+------------------+-----------+ | id | mail_domain | type | +----+------------------+-----------+ | 1 | my_app.test | EQ | | 2 | another_app.test | ENDS_WITH | +----+------------------+-----------+ I want to query this table depending on field type like this if 'type' == 'EQ' Model::where('mail_domain' , $search) if

Dunsti's avatar
Dunsti's avatar SinnbeckEloquent
7

Laravel Query Scope Issue

I am using query scope its working fine when I use it like Customer::phone($search)->agency()->get(); but give error when using agency scope first error: Non-static method App\Models\Customer::agency() cannot be called statically Customer::agency()->phone($search)->get(); public function scopeAgency($query) { return $query->where('agency_id', sessio

AbdulRehmanDar's avatar
AbdulRehmanDar's avatar abdulrehma...Laravel
2

How to make nested whereHas eloquent query

This is is the current query $userId = 1; $projectId = 1; return Kanban::where('project_id', $projectId) ->whereHas('cards.users.user', function ($query) use ($userId) { $query->where('user_id', $userId); }) ->get(); I am trying to find -> all kanbans where projec

Jakub003's avatar
Jakub003's avatar Jakub003Eloquent
5

Laravel Slow Wherehas Query in where sub query

I am trying to achieve the following code, my ajax is taking more than 1.50 minutes to respond, please guide me where I am going wrong $members = (new Member)->newQuery(); $members->with('address'); if ($request->filled('search')) { $search = $request->search; $members->where(function ($q) use ($search) { $q ->Where('name', 'LIKE', '%' .

kuns25's avatar
kuns25's avatar SinnbeckLaravel
7

Caching query data for a single request

I have a model called Product and a model called Price. Each product has its own price, which is not related to the Price model. The Price model is an independent set of data managed elsewhere. In my Price table I have a list of original prices alongside a replacement price, for example: Original Price New Price 100 250 500 599 1000 1200

Garet's avatar
Garet's avatar tykusLaravel
4

How to query nested eloquent models using slugs

Hi everyone! I have three models with the following structure: 1. Course 2. Chapter 3. Lesson I want to have friendly urls so I used the getRouteKeyName method to set slug on each model for route model binding: My problem is that when I start querying for lessons using the chapter slug, I get lessons from other courses which have the same chapter slug. Route::get('

pkabore's avatar
pkabore's avatar tykusEloquent
3

Query in Livewire

Hello everyone! to try something new and get out of my comfort zone I decided to try Livewire for my backend. The problem is that I can't get the Query to give a correct result, probably because I don't know something fundamental and because I got "bad habits" with how simple it is to use Yajra. In short, I am trying to do a database search on a dataset that must be p

Natxlie_In's avatar
Natxlie_In's avatar SinnbeckLaravel
1

Using query scopes vs. methods on Eloquent models.

I am having trouble wrapping my head around this... If I have an eloquent model called "Business" and each business can have a schedule, what I do to get the entire schedule is this: $business = Business::find($id); $schedule = $business->schedule; The definition of schedule is in the BusinessController, like so: /** * gets the business's schedule *

garrettmassey's avatar
garrettmassey's avatar Elliot_put...Eloquent
2

Is this usage of query scopes correct?

I have a model with three visibilities; public (for everyone, regardless of authentication status) private (only for the creator) auth (for every authenticated user) To simplify this logic, I figured I'd use query scopes, however I've never used them before so I'm wondering if I'm using them correctly. My model's code (irrelevant bits omitted) class MyModel extends Model {

lolsokje's avatar
lolsokje's avatar lolsokjeLaravel
3

Where to store query builder methods?

Hey guys, My app have Users and Badges. A Badge is simply a reward when you achieve some actions into the app. I have a Controller to show all badges, with pivot data about user progression/achievements. We can also filter this list and, for doing that, I'm using laravel-query-builder package from spatie. Here is my code for now: class Badge extends Model { public function

DimZ's avatar
DimZ's avatar DimZLaravel
1

Wants to create a query.

Hi there. I want to create a query. I have a model PropertyDeal which have columns agent_commision, agency_commision, user_id have more but need these right now. what I want is to display records in the table like this AGENT DEALS CLOSE DEALS MISSED COMMISSION John 5 3 5000 Hamza 9 3 9000 can get close deal by checking h

AbdulRehmanDar's avatar
AbdulRehmanDar's avatar SinnbeckEloquent
11

Best practise for DB query builder location

I have an index method in a resource controller that uses a DB query builder, the results of which are used in a view. Currently the DB query builder is sat in my controller, obviously this is not the place for it. Where is best practice for this to go? Am leaning towards the model. Do you have any advice, or is it a thumbs up to go with the model? Thanks

sidewaysglance's avatar
sidewaysglance's avatar SinnbeckLaravel
12

how to update jsonb column using laravel query

I want to update the available to false where toy->id=27. Can any give me the laravel eloquent query for this? Thanks. { "color":"red", "toy":{ "id":27, "name":"Truck", "price":12, "available":true }, "quantity":"12" }

belt's avatar
belt's avatar SinnbeckEloquent
3

count from pivot table using query builder

I have this eloquent query: $domains = Domain::with('users') ->withCount('contracts') ->withCount('users') ->orderBy('id', 'DESC') ->paginate(10) ->through(fn($domain) => [ 'id' => $domain->id, 'name' => $domain->name, 'numberOfContracts' =&g

calin.ionut's avatar
calin.ionut's avatar idewEloquent
5

how to represent this query in my views

How can I display the query as a date with items underneath? $sort = File::latest()->get()->groupBy(function($item) { return $item->date; }); dd result Illuminate\Database\Eloquent\Collection {#1227 ▼ #items: array:2 [▼ "2021-10-26" => Illuminate\Database\Eloquent\Collection {#1215 ▼ #items: array:1 [▼ 0 =&g

tomasosho's avatar
tomasosho's avatar SinnbeckCode Review
1

WhereHasMorph query doesn't work with custom morph types

I'm using different allies for morph Relations with morphMap(). When I use WhereHasMorph() and give my custom morph type, it doesn't work. in Controller : $data = Product::whereHasMorph( 'productable', ['PrintedBook'], // Book::class function (Builder $query, $type) use($title){ $query->where('title', 'LIKE', "{$title

Reundo's avatar
Reundo's avatar ReundoEloquent
0

How to receive nested db result in laravels query builder

Hi I'm struggling with Laravels Query Builder. My question: Is there a way to retrieve "nested" results as shown in the first case (using Eloquent) when I'm using Laravels Query Builder (second case)? First case: If I'm using the following Eloquent query, I receive a "nested" result: Shift::with('users')->get(); //Result: Illuminate\Database\Eloquent\Col

2108web's avatar
2108web's avatar SinnbeckLaravel
9

Validate existence of query string?

I want to have a couple of query string params validated (so they are required). For example, in my controller I could check: request()->has(['filter.type', 'filter.id']) I'm tempted to place this as a FormRequest but feels slightly off as it's not a form. Where would you place code that validates the query string?

panthro's avatar
panthro's avatar SnapeyLaravel
3

Strange error sql query

Hi! This funtion in laravel-model dosent work: public static function getgpsdataplayer($request,$golfID) { $flagget=$request->flagg; $green=$request->green; $orange=$request->orange; $red=$request->red; $judge=$request->judge; $toilet=$request->toilet; $holenumber=$request->holenumber; $dl=

birdietorerik's avatar
birdietorerik's avatar SinnbeckLaravel
5

What is wrong with this query ?

Hi! Have this function in laravel-model public static function getgpsdta($request,$golfID) { $flagget=$request->flagg; $green=$request->green; $orange=$request->orange; $red=$request->red; $judge=$request->judge; $toilet=$request->toilet; $holenumber=$request->holenumber; $dl=$request->

birdietorerik's avatar
birdietorerik's avatar SinnbeckLaravel
4

condition based query

i have a tables like below, rooms id, roomname,capacity 1, Room1, 4 room_slots id, room_id,from_slot, to_slot 1, 1, 9:00:00, 10:00:00 2, 1, 11:00:00 13:00:00 room_test_bookings id, room_slot_id, booking_date,test_id So here requirement is like below in booking page, select the date. Display all rooms in select box and user selects any one room based on room id i should fetc

Deekshith's avatar
Deekshith's avatar DeekshithLaravel
3

make query with if condition in controller

i have 3 tableles, i want to make a join query, but i want use if condition to check if the value of input sent in $request from the form if it''s not null i want to add whele contition: my request is: $query = DB::table('state') ->join('merchant','state.merchant_id', '=', 'merchant.id') ->join('entity','state.entity_id', '=', 'entity.id') -

Albertiss's avatar
Albertiss's avatar AlbertissCode Review
5

What is wrong with this query ?

Have this query; SELECT regdate , MAX(id) AS id FROM gpstrackers WHERE regdate BETWEEN (‘2021-11-22 03:00’ AND ‘2021-11-22 09:00’) GROUP BY flight Gives me this error; SQL-spørring: Dokumentasjon SELECT regdate, flight , MAX(id) AS id FROM gpstrackers WHERE regdate BETWEEN (‘2021-11-22’ AND ‘2021-11-22’) GROUP BY flight LIMIT 0, 25 #1064 - Something is wrong in your syn

birdietorerik's avatar
birdietorerik's avatar birdietore...Laravel
2

Add ->index to the DB query builder

First, thanks for the fantastic framework. I am thinking of using a specific index in the DB:: query builder and searching the options in the DB:: class but it seems that there is no ->index or ->useIndex in the options. Is there any way that I add it as an (optional) option to DB query builder?

hameti's avatar
hameti's avatar SinnbeckLaravel
5

How to write query to exclude specific matching records

I have this mock table rich_guys. Notice there are records with the same slug 1|6 & 3|5. Also 2 slugs have a foo_id 3|6. +----+------------------------------+------------+ | id | slug | foo_id | +----+------------------------------+------------+ | 1 | bill-gates | NULL | | 2 | jeff-bezos | NULL |

daugaard47's avatar
daugaard47's avatar Tray2Laravel
7

Correct query to count with relation

I am working on a real estate site. Need the count of listings in a project ( building ) of a particular city. $listingcount = App\Models\Listings::where('project.city',$item->id)->count(); Listings model has a relation to project() and projects has a relation to city(). What is my correct query to count ?

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
3

Retrieve comments and its count() by paginate in one query

Hello guys, I want to retrive comments and comments_count but I want to know that is it possible to do this in one query? In controller I have this $comments = $product->comments() ->with(['user', 'rating']) ->withCount('replies')->paginate(5); $comment_count = $product->comments()->count(); Can I do this in one query? or is there a better way to do this?

Reundo's avatar
Reundo's avatar MichalOrav...Eloquent
1

How to create a raw query for substring but portable?

I need to create a substring(field, 0, -10) to keep only last 10 chars of a string. I am using a raw query here because i need to do a lower(field) LIKE this because i am using both mysql and postgres (some customers have mysql and others postgres). I remember there was a system to use "getSubstringExpression()" of laravel, that is automatically passed to specific gra

realtebo's avatar
realtebo's avatar SinnbeckEloquent
4

Filter query relation belongsToMany for column on related table

I've database with a 3 tables. Campaigns Subscribers Campaign_subscribers (pivot table) campaign public function subscribers(): BelongsToMany { return $this->belongsToMany(Subscriber::class); } subscriber public function campaigns(): BelongsToMany { return $this->belongsToMany(Campaign::class); } CampaingSubscriber public function campaigns() { return $thi

abkrim's avatar
abkrim's avatar abkrimEloquent
2

Query selected and unselected dropdown

Hi, I'd like to search or filter talents based on selected dropdown. But, In this case several dropdown are not mandatory. How to query selected and unselected dropdown's value? This is my route Route::get('/hav-box', [TalentSearchController::class, 'searchTalent']); This is my method to search or filtering public function searchTalent(Request $request) { //code he

jufrensius's avatar
jufrensius's avatar Moore85Eloquent
3

Laravel Query Value Need To Explode

Hi all , I have a laravel Query, where it has a value , i need to explode the vlaue and write a where condition. Her is the code dd($appointments->whereIn([explode(' ','preference->appoint_info->location_dropdown')],'ssss')->get()); Can some one help me with these Thanks

ilhamzacky's avatar
ilhamzacky's avatar SinnbeckLaravel
20

Query Builder custom sorting pre-pagination

I have a livewire component that loads all records and then sorts the collection based on whether a query param is present. This works great as intended, where the "active" record becomes the first record listed, but I haven't been able to get pagination working when I need to sort, pre-pagination. use App\Models\MyResults; use Livewire\Component; use Livewire\WithPa

whoisthisstud's avatar
whoisthisstud's avatar whoisthiss...Code Review
12

Laravel Query Procedure

what's wrong with my query procedure $database = DB::connection("mysql_absen")->select(DB::raw('CALL `get_attendance_total_by_location`(@p0); SET @p0='%12-10-2021%'')); i got error like this ErrorException A non-numeric value encountered

swarley_HIMYM's avatar
swarley_HIMYM's avatar swarley_HI...Laravel
2

Advanced Search Query with Eloquent

I have a mobile phone specs website. I'm using the following query it returns results correctly: $q = 'oppo reno'; Phone::where('name', 'like', sprintf('%%%s%%', $q))->get(); However, if we change the position of words of the search query to the following, it won't return any results. $q = 'reno oppo'; Phone::where('name', 'like', sprintf('%%%s%%', $q))->get(); Any help

cclegend905's avatar
cclegend905's avatar cclegend90...Eloquent
8

exists() query condition in left join

Hello. I'm having a query that is based on GET parameters. In short, if some parameters (checkboxes) are set the query is changing. But now I need a little bit different structure for one of the checkboxes. When checked - I need to check if there is an existing row with a specific matching column (detail_id) value from another table. I tried with: $upgrading->leftJoin('ano

lat4732's avatar
lat4732's avatar LarAlexLaravel
4

Building an Eloquent Query based on data in model instance

I am building a system where administrators can create "questions" as part of an automatic quiz. It is really more of an audit so that administrators can create items to see if data in the table exists or is a certain value for all data. here is an example of a question model instance: #original: array:9 [▼ "id" => 1 "name" => &quo

garrettmassey's avatar
garrettmassey's avatar SinnbeckEloquent
5

Eloquent or DB query products and menu items

i have a query where i want to load 10,000 products from the table. then every product hasmany categories ln a category table with the columname of name. $basicObjects = DB::table('products') ->leftjoin('assortments', 'assortments.product_id', '=', 'products.id') ->leftjoin('prices', 'prices.product_id', '=', 'products.id') ->

theProfit's avatar
theProfit's avatar SinnbeckEloquent
1

How to refactor query eloquent collection

Hello! I have query which is heavily expanded and i wonder how i can refactor them. Any ideas? :-) $controls = Control::with(['shop', 'coordinator']) ->where(function ($query) { $query->whereDate('control_end_date', '>=', today()->subWeeks()->startOfWeek()->format('Y-m-d')); $query->where(function (Builder $qu

Sinres's avatar
Sinres's avatar SinresEloquent
8

Report Generation Tools (Query to PDF/CSV)

Hi, are there any in-built or good libraries which you'd recommend for Laravel to format/style data into a pdf (generate a report) based on a query?

lukegalea16's avatar
lukegalea16's avatar MichalOrav...Laravel
1

Dynamically activate navbar ignoring query string

Hello, I am trying to activate the current navigation button depending on the active request using {{ Request::is(dashboard/xxxx) ? 'active' : '' }} I have few pages in the dashboard and some of them have tables, those tables use query strings to identify the page, orderBy specific column and order direction (asc, desc) The menu get activated properly if there is no query strin

Jecs9's avatar
Jecs9's avatar Jecs9Laravel
12

Query Builder with WhereIn ANDs

I'm wondering if anyone has any suggestions on how I might convert the following example SQL to Query Builder: SELECT DISTINCT p.product_id FROM products AS p WHERE p.product_id IN ( SELECT product_id FROM product_components WHERE product_id = p.product_id

sparkymain's avatar
sparkymain's avatar sparkymainLaravel
21

How to query whereIn AND whereIn AND whereIn in Query Builder?

I want to do this query in the query builder. select * from users where 'value' in (firstname) and 'value' in (middlename) and 'value' in (lastname) Right now my code looks like this: $users = DB::table('users') ->whereIn('firstname', [$teacher->firstname]) How do I implement AND on this query?

kramsuiluj's avatar
kramsuiluj's avatar kramsuilujLaravel
8

Add a Count Only when query params include "?count"

How can I add a total count on a Laravel API response only if there is a query params? example: /?count(orders)

nobodyownz's avatar
nobodyownz's avatar nobodyownzLaravel
2

Raw SQL query to eloquent

Hello laravel users! So i'm trying to fix a tool that matches the SQL search and return a match by percentage. I've tried and used this SQL to achieve what I want. SELECT (company) , MATCH(company) AGAINST('SEARCH STRING') as relevance , b.maxrelevance , (MATCH(company) AGAINST('DATABASE ENTRY TO BE SEARCHED'))/b.maxrelevance*100 as relevanceperc FROM watchlists a , (SELECT MAX

libellux's avatar
libellux's avatar libelluxEloquent
2

Issue combining single instance model relation and collection model query in same controller action

I have an issue in a controller action where I'm trying to get both a single instance of a model via a relationship, as well as a broader query of that model via a general eloquent builder. Example code from inside my controller function: $client = $template->client; $allClients = Client::where('company_id', $company->id)->get(); return view('templates.new-project')-&g

markcampbell's avatar
markcampbell's avatar jqodirovLaravel
1

Use Pagination on Query Builder Instance

I am using Laravel Framework 8.62.0 and PHP 7.4.20. I get the following error: Call to undefined method Illuminate\Database\Eloquent\Builder::links() (View: /home//Code/test_project/resources/views/index.blade.php) I have a view that has uses 3 simple filters. To display the view without filters I use the following: public function getSearchView() { try {

GregorSams's avatar
GregorSams's avatar jlrdwLaravel
2

Mocking facade DB query builder `when`

Hi, I am using laravel mocking of facade to unit test my query. but I cant continue because i dont know how to create a mock for when. does anyone here have experience doing it? see code below for the example.. DB::table('users') ->join('profiles', 'profiles.user_id', 'users.id') ->when(true, function ($q) { $q->where('profiles.age', '>', 20); })->get(); b

marbobo's avatar
marbobo's avatar Tray2Testing
6