DB Query Builder to Resource

Hi Guys, Sorry if this sound dumb but I have not used DB query builder extensively and used eloquent most of the time. I have this requirement where I want to use query builder along with several joins etc. so my query looks like this so far [ { "user_id": 20, "first_name": "Zeeshan", "last_name": "Sabri",

Zeesab's avatar
Zeesab's avatar ZeesabLaravel
2

LEFT JOIN Query Builder and SQL

Hi, I'm struck on the last step of getting a query to work. I have an instance of query builder, which I need to LEFT JOIN with an SQL SELECT. It's pretty complex, so I'm trying to avoid pasting it all in here. I will try and simplify. I have something like: 1, $SQL = 'SELECT * FROM things' (greatly simplified) 2, $query (Query Builder instance) and I want to end up with somet

phpMick's avatar
phpMick's avatar phpMickGeneral
2

Hide query param in URL

Hi, I'm wondering if there is some way to hide the query params in URL when using Inertia Link? For example I want to get all available cars with a click of a button, but I don't want the URL to look something like this: /cars?searchTerm[fromDate]=2022-08-18T21%3A47%3A43.112Z&searchTerm[fromTime]=12%3A00&searchTerm[toDate]=2022-08-18T21%3A47%3A43.112Z&searchTerm[toT

hjortur17's avatar
hjortur17's avatar SinnbeckInertia
2

Get value if not null in the same line of query

Using Laravel 6 and PHP 7.4, is it possible to check the following for non-existing data before accessing the value but together with the query? In order to avoid some cases of "Trying to get property of non-object" (Two examples, one with query builder and one with eloquent) DB::table('table')->where(...)->value('column'); // or Auth::user()->someRelations

Ligonsker's avatar
Ligonsker's avatar winglyCode Review
7

Laravel - Display query result for at least one week (query)

I am using Laravel 8 Writing a query, and want to display today's record(Dt.: 17/08/2022) for at least one week which means(at least for 7 days (Dt.: 24/08/2022)) using carbon. After one week which means on 25th august records should be excluded from listing. What should I do to archive this result? any help would be great! Thank you!

M@rty's avatar
M@rty's avatar Tray2Laravel
5

Is it possible to use Auth with query builder DB::table?

I want to query some table but also make sure the user is authenticated is it possible to use both Auth and DB::table? Or do something that would be equivalent to that? because I want to create an accessor for the User which gets data from another table: DB::table('table')->where('id', some_id)->value('colum'); I was thinking about: DB::table('table')->where('id', Aut

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
2

How to sort a Laravel query builder result by multiple columns?

I want to sort multiple columns in Laravel 4 by using the method orderBy() in Laravel Eloquent. The query will be generated using Eloquent like this: SELECT * FROM mytable ORDER BY coloumn1 DESC, coloumn2 ASC How can I do this?

JohnSonandrla's avatar
JohnSonandrla's avatar SinnbeckEloquent
1

Query Building

Can you explain this query please: "where('city', 'new york')->orderBy('name')get->()" ? $student=Student::where('city', 'new york')->orderBy('name')get->();

mahsanr44's avatar
mahsanr44's avatar mahsanr44Laravel
8

How do add (sum) a column value with other value in query builder laravel?

in MySql: SELECT *, cost_value + 1000.00 final_value FROM kits WHERE CODE = '231486V' But in Laravel Query: DB::table('kits') ->select('*') ->select(DB::raw('SUM(cost_value + 1000.00) filnal_price')) ->orderBy('cost_value') ->get(); not work In Laravel documentation not found a soluction.

rodrigodonha's avatar
rodrigodonha's avatar MohamedTam...Laravel
1

Get request data inside query using when

hello , i have search pipe here like public function search(Request $request) { $data = $request->all(); $request->request->add(['filter' => 'advanced']); $title = "clients"; $query = Client::query(); $client_types = ClientType::whereStatus(true)->get(); $labels = Label::all(); $flags = Flag::all(); $query->when($

MahmoudAdelAli's avatar
MahmoudAdelAli's avatar derbaLaravel
8

Sub Query / Union Help / Legacy

Hi Friends, I am running in to some issue in converting a legacy query using query builder and would appreciate any advice. public function getData($player) { $one = DB::table('test') ->selectRaw('team, count(user) as games') ->where('Player', $player); $stats = DB::table('test') ->select(DB::raw

thatsnotwherethecartsgo's avatar
thatsnotwherethecartsgo's avatar thatsnotwh...Laravel
0

WhereHas with DB Query Builder

Hey! I need to make this search functionality also search through tags. \DB::table('posts') ->where(function($query) { $query->orWhere('published_at', '<', now()) ->orWhereNull('published_at'); }) ->where('moderated', \DB::raw('true')) ->where(function($query) use ($request) {

lat4732's avatar
lat4732's avatar SnapeyLaravel
4

Global query scopes preventing seeding of model relationships

I am encountering a problem running a seeder on a model and its relationships. I have a global query scope that uses a session variable that does not exist in this context which causes the seeder to fail. Is there any way to bypass the global query scope when seeding? 'withoutGlobalScope' does not seem to work in seeders or factories or perhaps I'm not understanding how to use

gvdspuy's avatar
gvdspuy's avatar gvdspuyLaravel
11

MySQL Subquery in Query Builder

Can someone please explain how to do this in Query Builder: select * from ( select * from a_table ) a inner join another_table at on at.id = a.id; Obviously greatly simplified.

phpMick's avatar
phpMick's avatar phpMickGeneral
2

Query result with deleted_at

Hello, everyone I have a problem with the result with one of my queries. There is two database tables with schema like bellow Schema::create('post_xrefs', function (Blueprint $table) { $table->id(); $table->foreignId('user_id')->constrained()->onDelete('cascade'); $table->string('type', 32); $table->integ

HristoMihaylov's avatar
HristoMihaylov's avatar SinnbeckEloquent
17

Raw Sql to Laravel query builder

This is my raw Sql running properly in my sql. SELECT * FROM `created_tags` WHERE `expire_date`= DATE(SYSDATE())+INTERVAL '22' DAY AND `is_registered`='1'; I want to convert this into laravel query builder, Please Help I tried doing this below but it is wrong DB::table('created_tags')->where('is_registered', '1') ->whereRaw('expire_date = DATE(SYSDATE

SurajKumar's avatar
SurajKumar's avatar petritLaravel
6

slow query when using order by clause and group by clause

slow query when using order by clause and group by clause Query taking 28 sec to execute. select DISTINCT home_list.id as 'property_idlist',home_list.address as 'property_addr',home_list.city as 'property_city',home_list.county as 'property_county',home_list.state as 'property_state',home_list.zipcode as 'property_zipcode',franchise_lead.request_home_address as request_home_add

Mahaveer's avatar
Mahaveer's avatar Tray2Eloquent
1

addSelect the count of multiple rows to a query

Hello there, I'm trying to retrieve all clients and the count of the location for each client using the Database query builder. One-to-many relation between the client and locations. I'm doing this: use Illuminate\Database\Query\Builder; use Illuminate\Support\Facades\DB; DB::table('clients') ->addSelect(['client' => function (Builder $builder) { $builder->select(

Abdulsalam's avatar
Abdulsalam's avatar SinnbeckEloquent
7

eloquent model query in component class - getting: Cannot declare class App\View\Components\componentName, because the name is already in use

so i want to pass a value to a component and in the components class i want to do a query on a model depending on the value that was sent to the class and then have the results of the query sent to the component blade.php

uloncl's avatar
uloncl's avatar nebulous75Code Review
9

How can I do an orderBy query for data that is stored in JSON format?

So for example when the data is in string format I can do something like this: ->orderBy(DB::raw('FIELD(animal_type, "fish", "amphibian", "reptile", "bird", "mammal", "") ASC, animal_type')) But if the data for 'animal_type' is stored in JSON format like this: ["vertebrate", "amphibian"] Let's

rotaercz's avatar
rotaercz's avatar jlrdwLaravel
7

What will be the eloquent query with three relations ?

I am developing a real estate site. A User can join any number of INNER CIRCLES ( something like groups ). Now whenever a User list a property , I need to send emails to all the members of all the circles he has joined. What will be the query ? User model : public function circlemember() { return $this->hasMany(InnerCircleMember::class, 'member_id', 'id');

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
19

An eloquent query with 3 models.....

A User can join any number of INNER CIRCLES ( something like groups ). Now whenever a User list a property , I need to send emails to all the members of all the circles he has joined. What will be the query ? Model User public function circlemember() { return $this->hasMany(InnerCircleMember::class, 'member_id', 'id'); } Model InnerCirclemember pub

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
2

What is the correct way to use relation in query ?

'User' has one to many relation with 'Follower'. I need to send email to all the followers of the auth user. what will be the correct query ? User Model public function followers() { return $this->hasMany(Follower::class, 'brokerid', 'id'); } Follower Model public function broker(){ return $this->belongsTo(User::class,'id','brokerid');

FounderStartup's avatar
FounderStartup's avatar anilkumart...Laravel
10

Aggregates as part get in an Eloquent query

Howdy, all, am having a little trouble with an Eloquent query that uses multiple models. The trouble lies in gathering one of the returned columns, which is an aggregate. Tables: Model1 -> Contains foreignID fields for model2_id and model_id. Model2 -> Whose ID field will match to model1.model2_id. Model3 -> Whose ID field will match to model1.model3_id. Model4 -> C

osxtra's avatar
osxtra's avatar SinnbeckEloquent
8

Query Builder -> middleware -> pagination

Hi, I don't understand how to work with data getting from query Builder to modify it. Here is something like: I get data and send to blade with pagination $invoices = $this ->startConditions() ->select($columns) ->orderBy('invoices.id', 'desc'); return view('livewire.invoice.list', [ 'invoices' => $this->invo

eddstep's avatar
eddstep's avatar eddstepLaravel
11

The query in the controller returns very slow results

After I checked mysql-slow.log, the results returned from the queries due to this controller are very slow (average takes about 58 seconds for a query). Please help me optimize these queries. The dev team has abandoned my project without support anymore, I'm really stuck. public function topFavorite(Request $request) { $stories = Story::select(['id', 'name', 'slug', 'source

hoangvnn's avatar
hoangvnn's avatar hoangvnnRequests
2

Change the query to Builder query

I discovered that the dev team on my project writes a database query that takes a long time to return results (average about 58 seconds for a query like this): select `id`, `name`, `slug`, `source_id`, `status`, `author_id`, `total_words`, `view`, `cover`, `desc`, (select count(*) from `transactions` where `stories`.`id` = `transactions`.`story_id`) as `transactions_count` from

hoangvnn's avatar
hoangvnn's avatar hoangvnnRequests
5

Multiple Models into one predefined query

Hi forum, I've got multiple Models which each are part of one big Product. Lets say i've got the following Models: Product (Contains the basics of ID, price, category id, etc.) ProductStatus (Whether it's active or not) ProductDescription (Name, Description, Language ID (Multiple rows for one product, but differentiated by the language id)) ProductStock (Keeps track of the stoc

thoba's avatar
thoba's avatar sr57Code Review
1

How to query a 'date_time' field for a 'date'

I am trying to get a list of records that are for a certain date ($most_recent_date = '2022-06-22') Here is an example of the field 'date': 2022-06-21 00:00:00 2022-06-21 01:01:01 2022-06-21 02:02:02 2022-06-20 03:03:03 2022-06-20 00:00:00 2022-06-22 00:01:01 2022-06-22 00:02:02 I want 2 records: 2022-06-22 00:01:01 2022-06-22 00:02:02 How would I write this query? I tried th

scottsuhy's avatar
scottsuhy's avatar scottsuhyLaravel
4

No query results for model [App\Models\User]

getting this error please how can i solve this No query results for model [App\Models\User] class Settings extends Component { public $user, $bank_name, $account_name; public function mount(Users $user) { $this->user = $user; } public function update() { $this->validate([ 'account_name' => 'required|min:5',

ene's avatar
ene's avatar eneLaravel
6

Nested query with OR ?

Controller : $members = InnerCircleMember::where('circle_id', $id)->where('status', 1)->paginate(10); $listingsforsale = Listings::where('user_id',$user->id)->where('expiry_date','>' , now())->where('listingtype', 1)->where('status',1)->paginate(10); I need $listingsforsale where 'user_id' is equal to ( $members->member_id OR $members->admin_id )

FounderStartup's avatar
FounderStartup's avatar FounderSta...Laravel
15

How to handle an empty search query?

Hi, I'm currently trying to implement a search functionality in my API. Unfortunately, when I leave an empty query the API returns absolutely nothing. Any idea what I'm doing wrong? Route: Route::get("/products/search/{title?}", "searchTitle"); Controller function: public function searchTitle(Request $request, $title = "" ) { $pageSize = $requ

anton123's avatar
anton123's avatar anton123Laravel
4

Help converting MySQL query to laravel query

Hello, I am stuck and cannot figure out how to convert this query into a laravel query. Maybe I'm overthinking it or just having a blank moment. Here's the query: SELECT salesperson_id, SUM(CASE WHEN salesperson_two_id IS NULL THEN 1 ELSE 0.5 END) totalDeals FROM ( SELECT salesperson_id, salesperson_two_id, sold_date FROM sold_logs UNION ALL SELECT salesperson_two_

RayC's avatar
RayC's avatar RayCCode Review
3

belongsToMany with table definition produces wrong query.

Laravel Version: 9.21.4 PHP Version: 8.1. Database Driver & Version: pgsql 13 Description: I'm currently migrating our application to use postgres instead of mysql. to make full use of the schema functionality we have to be more specific when using belongsToMany relationship to also include the table with schema prefix since we have a referees table in 2 schema and search

RonnieVisser's avatar
RonnieVisser's avatar RonnieViss...Eloquent
1

How to filter a query based on columns from foreign table?

I have movies and quotes table. quote has foreign key which references movie id. I need to write a query which gets a quote where movie name = 'foobar'. when i write a query like this Quote::with('movie')->find(16) I get back result { body: "quote", movie_id: 1, movie: App\Models\Movie {#4594 id: 1, name: "Jairo Abbott",

DavidBuchukuri's avatar
DavidBuchukuri's avatar DavidBuchu...Eloquent
2

Get Request Query Value as a string (Laravel/Php)

Hello, i'm working on vue + laravel, from vue i'm making get request (with query parameter) to laravel, as result i get something like feed?search=#somestring, so, in laravel i want to get this query parameter, but as a string, i can get it like request('search') and check if it exists or something, but cannot check for the string inside.. i want to check if string in query par

luarsab's avatar
luarsab's avatar luarsabLaravel
2

Constrain a with condition by the parent query

So i have a query to get a route someone has taken and where they vitisted Route::with([ 'streets', 'streets.identifiable' //Polymorphic relationship 'streets.identifiable.visits' // <- this is fetching all visits ])->where('status', 'complete') ->where('user_id', '12') ->get(); The problem im having is the street.identifiable.visit relationship is fetching a

JABirchall's avatar
JABirchall's avatar JABirchallEloquent
3

Constructing an Eloquent Query

I'm trying to construct an Eloquent query to prevent duplicate items in a database. I have a user ID column (foreign id linked to users table) and an item ID to query against. In my controller, I want to run an if statement that checks if the current user ID and Item ID are present, then don't save item, otherwise, proceed and save item to database. I've tried a bunch of differ

pfigdev's avatar
pfigdev's avatar pfigdevEloquent
4

Query many to many relation in Laravel Controller

Hello everyone, I'm a beginner in the coding world and I'm facing a problem I don't know how to solve. I'm building a simplified replica of AirBnB, I give the user the possibility to select some services to have a list of apartments with those services. I have an Apartments table and a Services table in my db, in between them there's a many to many relation, so there's the pivo

EuroFighter53's avatar
EuroFighter53's avatar SinnbeckEloquent
16

Same query ... in code and DB agent

Congratulations Encountered a strange problem The same query, in TablePlus' GUI returns rows While in the code returns an empty array. What could be the reason? SELECT * FROM `records` INNER JOIN `meta_records` AS `data0` ON `data0`.`record_id` = `records`.`id` INNER JOIN `meta_records` AS `data1` ON `data1`.`VALUE` = `data0`.`VALUE` WHERE `data1`.`record_id` = 116 and (

simcha's avatar
simcha's avatar simchaLaravel
2

String delimiters missing in INSERT query

Hi, I have a problem while inserting rows in a table: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (test_db.documents_rows, CONSTRAINT fk_dro_vat FOREIGN KEY (vat_type_id) REFERENCES vat_types (id`) ON DELETE NO ACTION ON UPDATE NO ACTION) insert into `documents_rows` (`document_id`, `product_id`, `product_name`, `vat_typ

robman70's avatar
robman70's avatar SinnbeckLaravel
19

Problem with filtering query

I have a table with search box and a checkbox button for adult users (age above 18). The users have a name and an email. The issue is when the adult checkbox is checked and you search by an email, it doesn't return any result. For example the following two rows in the database: Name Age Email Concepcion Turcotte 16 [email protected] Emmy Ho

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
4

Query optimization for applying multiple filters

I have a query for applying filters and search schools from table. I am getting all the variables through ajax request in laravel controller. I am using 'when' keyword for checking if that variable needs to be added in filter. Here is the query $schools = School::where('is_active',1) ->whereBetween('tuition',[$min_tuition_fee,$max_tuition_fee]) ->whereBetween('applicatio

swim360's avatar
swim360's avatar Tray2Laravel
8

Query Scope where $user->relationship->method() is true

I am trying to write a query scope to find all instances of the User model where the user's schedule indicates that they are available. I have the User model, and a UserSchedule model. the User model has a method called isAvailable() that returns true or false if the UserSchedule indicates that a user is available on the current day of the week. my UserSchedule: class UserSched

garrettmassey's avatar
garrettmassey's avatar psrzEloquent
5

laravel multiple table query issue

hello guys, i want output like { "success": true, "message": "user job list", "data": [ { "id": 2, "job_name": "electrian", "user_id": 1, "service_provider_ids": "28, 29", "created_at": "2022-07-12T09:27:12.000000Z", "up

vandan's avatar
vandan's avatar aliabdmLaravel
14

Inner join query in laravel

Hello help to create this sql query in laravel, thank you UPDATE students s1 JOIN students s2 ON s1.studentID = ? AND s2.studentID = ? SET s1.class = ?, s2.class = ? WHERE s1.id = ? AND c2.id = ? I have tried this: DB::table('students') ->join('students as s1', 's1.studentID', '=', $studentID1) ->join('students as s2', 's2.studentID', '=', $stu

anin123's avatar
anin123's avatar SinnbeckLaravel
9

How to make a query with relations and order it by date of the relation in Laravel 9

Hello, I have the User model, I also have the Post model In Laravel 9 I would like to get 4 users through the newest posts of my entire Post model (taking into account that even a single user could have n number of newest posts) and then get the posts of each user (two for each user) obtained through of the relationship, something like that in case it is not understood very wel

andrecuellar's avatar
andrecuellar's avatar jdc1898Eloquent
1

laravel update query if else

Hello It is possible make check inside update query I want make update 'priceA' if $checkPrice is more 100 Post::where('id', 1) ->update([ 'title' => $title; 'text' => $text; if( $checkPrice > 100) { 'priceA'=> $priceA } ]);

Laracast13's avatar
Laracast13's avatar sr57Laravel
9

How to do a surgery on an Eloquent Query on the fly

Hi, is there any way to interrupt and change the components and scopes of an eloquent query? in order to add multitenancy to an existing project, I've added a global scope to my models, filtering results by tenant_id. and it works fine. the problem is I have found more than 500 hardcoded 'where conditions ' and 'create statements' all over the place. such as these: $notificatio

MostofaL's avatar
MostofaL's avatar jlrdwEloquent
7

Query very slow in app but fast in console

I have the following query in a conroller: 'students' => Auth::user()->site ->students() ->leftJoin('student_meals as m', function ($join) { $join->on('m.student_id', '=', 'students.id') ->where('date_served', Request::only( 'serviceDate') ) ->w

mpk123's avatar
mpk123's avatar mpk123Laravel
0