sql to laravel query buider

Im trying to convert the below sql to a laravel query buider code but i dont know how to go about it. i have tried to use automated tool but none is giving me the correct output. SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM( CASE WHEN business_locations.location_id = "', business_locations.location_id, '" THEN variation_location_details.qty_available ELS

robertkimaru1998's avatar
robertkimaru1998's avatar robertkima...Eloquent
2

Query Users according to specific role (Laravel Spatie)

Hello, I have a really simple question and I am stuck on it. How can I get users that have only a specific role? i.e. I want to query users that have an "employee" role only but in general, they can have multiple roles. Example: User 1 - Employee - Accounts User 2 - Employee In this example, I only want user 2.

SalmanMalik's avatar
SalmanMalik's avatar SinnbeckLaravel
3

Laravel Query Error

i've 3 tables, User/Roles/role_user for multi roles user when i'm trying to access the roles function to get all users in blade i use in_array and its okay but i'm trying to get it in controller with whereIn but there's mistake in query so any hints. $usersIds = auth()->user()->role_ids; $Users= User::whereIn(4, $usersIds)->whereNotNull('email')->ge

karimali1337's avatar
karimali1337's avatar karimali13...Laravel
16

Php Query

Hi everyone, This query is regarding Php: Is there anyway we can convert Array A to Array B: Please advise. Thanks. Array A: array:9 [▼ "color_content" => "zinc-400" "color_headline" => "zinc-300" "color_stroke" => "#d4d4d8" "image_logo" => "https://bannertize.com/image/pushvertize.sv

geetpurwar's avatar
geetpurwar's avatar geetpurwarLaravel
4

Query results to array without key

How do I query a table for only the ID, and have it return an array without keys, and only the IDs. For example, I want an array like 1, 2, 3, 65, 77, 190, 234 ..... Thanks

lara68236's avatar
lara68236's avatar cservicesEloquent
2

add to query result to anothor

Hi, I want to query something like this $v1 = Model::where('x',1)->inRandomOrder()->limit(2)->get(); $v2 = Model::where('x',2)->inRandomOrder()->limit(4)->get(); how to add the result of v1 & v2 in one object $v= $v1+$v2; how can i do that ? thanks edit.., I wasn't clear. I want to choose randomly 5 or 3 or 10 users who have a higher score... my approach

ashoor's avatar
ashoor's avatar ashoorLaravel
5

Query to show listing with responds on top ?

Need to show listings with RESPONDED listings on the top. $openlistings = Listings::where('expiry_date','>' , now())->where('dealstatus', 1)->where('status', '1')->where('user_id', $id)->paginate(10); What will be the correct query ?

FounderStartup's avatar
FounderStartup's avatar SinnbeckLaravel
18

Help required to minimize query duplication

Here, is the exact query, SELECT Ifnull(Round(Sum(b.revtotal),2),0) AS comrevenuew, ifnull( ( SELECT Sum(daily_target) FROM `live_statsb` WHERE type= '".$businessunit."' AND ( businessunit LIKE '%".$bu."%' OR businessunit LIKE '%".$bu2."%')),0) AS goal, round(ifnull(round(sum(b.rev

Inquisitive's avatar
Inquisitive's avatar rodrigo.pe...Laravel
1

Is there a new fancy way to query by relationship?

Hey folks, is there some cool Eloquent method to query, let's say, Dogs where Owner's ID is equal 3 or barked_at IS NULL? Silly example, but I'd like to understand how to query for relationship in a fancy way. I can't use User::find(3)->dogs(), since I also want all dogs that have barked_at IS NULL. I did this: $dogs = Dog::whereNull('barked_at') ->orWhereRelation('ow

RoboRobok's avatar
RoboRobok's avatar kevinbuiEloquent
7

query improvement

Hi, I need assistance in improving my query. what I'm doing getting nested self-relation records. here is my migration. Schema::create('menu_items', function($table) { $table->increments('id'); $table->string('name'); $table->string('url'); $table->integer('parent_id')->unsigned()->nullable(); $table

rehman_invozone's avatar
rehman_invozone's avatar SinnbeckEloquent
7

Pagination error when trying to limit the query results. Method Illuminate\Database\Eloquent\Collection::links does not exist.

Pagination works perfectly when including ALL results of a query, but errors out with the following error (below) when I try to exclude a few results in a query. BadMethodCallException: Method Illuminate\Database\Eloquent\Collection::links does not exist Example #1: Here is the controller code that works class UserController extends Controller { public function index() {

Penkowsky's avatar
Penkowsky's avatar PenkowskyLaravel
3

Adding commas to SUM in raw query

I have a few columns that I caclculate their SUM using raw query: MyModel::select(DB::raw(SUM(col1) as col1_sum, SUM(col2) as col2_sum)))->groupBy('col1_sum', 'col2_sum')->get(); Is it possible to add commas whenever needed (every 3 digits from the right if exist) It is going to be displayed in Blade. Is my best option to use a new Blade directive, or there's a better op

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
4

Cannot enqueue Query after invoking quit

I'm trying to connect to the database of my nodejs dockerized project and I'm getting this err that I don't understand, I've googled but I can't find a solution. I am using typeORM and dokcer for my project. I share the error, thanks in advance for any help. query: 'SELECT VERSION() AS `version`', [1] parameters: undefined, [1] driverError: Error: Cannot enqueue Query afte

Alejo's avatar
Alejo's avatar AlejoGeneral
3

What's wrong with this query?

Here is my query Model::where('artist_token', $token) ->update([ ['artist_token' => "000"], ['account_token' => "000"] ] ); And here is the error SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: update `models` set `0` = {"artist_token":"

Mick79's avatar
Mick79's avatar Mick79Eloquent
2

How to test filter query

Hello, How can I test unit filter query?

temo's avatar
temo's avatar kevinbuiLaravel
23

Laravel raw query related field calculation

I wanna filter customers based on balance (which comes from the related 'ledgers' eloquent model table which has 'customer_id', 'amount' and 'type ' fields (type=1 credit and type =2 debit)) my codes are as // Balance Filter if ($this->min_balance) { $this->customers = Customer::whereHas('ledgers', function ($query) { $query->select

ilearnbydoing's avatar
ilearnbydoing's avatar ilearnbydo...Eloquent
8

Query Assistance

I need to form a query but planning it out and still can not seem to think a way to do it. User has Many Subscription So i need to find all User(s) who's last active subscription is older than a year. Subscription have. starts_on and expires_on fields to use Something like orderBy expires_on DESC and then filter thru the collection to return if older than a year? But probably n

movepixels's avatar
movepixels's avatar movepixelsLaravel
8

Is it possible to "mimic" group_by and order_by after a query?

I have a base query which needs to be done a few times with the only difference between them is the queryBy and orderBy columns. The results are supposed to be loaded into the same page. So I don't want to do separate queries because it will take time For example, this is the base query: MyModel::where() ->select(DB::raw('SUM(col1) as col1, SUM(col3) as col1, SUM(col3) as co

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
3

query performance

DB::table('usermetas')->where('status', 1) ->select('browser', DB::raw('count(*) as total')) ->groupBy('browser') ->get(); now its query time 1.65 sec it's possible to decrease query performance?

ga46's avatar
ga46's avatar rodrigo.pe...Eloquent
3

limit results based on the existence of a sub query column

Hi, can someone help me, I just need to limit the results based on the relation sub query column (task_action). here's my current query $orders = Order::select('id') ->with(['templateIndexOne' => function ($q) { $q->addSelect(['task_action' => Task::select('action') ->whereColumn('mapping_id', 'template_

Szyfr's avatar
Szyfr's avatar SzyfrEloquent
5

Livewire query string image lazy loading problem

hi Guys im trying to lazy load images with livewire. im using javascript lozad (the same problem with any lazy load), so when the page load first time every thing works fine image lazy load and show like it should, but when i use query string to filter data, like search or every operation the image not show and show blank, its like javascript not working any more.

kaiserkais's avatar
kaiserkais's avatar rodrigo.pe...Livewire
1

livewire optimize query structure (lower amount of queries)

Assuming the following code: $transactions = OrderTransaction::with(['order:id'])->whereHas('order', function ($query) use ($search) { $query->where('currency', 1); })->latest(); if($this->search !== '') //if we query for order transactions, we can search either for address, paymentRef or by ID

t0berius's avatar
t0berius's avatar achatziEloquent
7

Any hacks or speeding up a query with multiple joins?

This is a more theoretical question. I have multiple data tables: users, user_details, projects, project_details,... I need to make an overview page, where I would paginate by users. In past, I would create eloquent relationships, and pull the data. But, on this overview page, sorting needs to be done on columns, which are spread across every mentioned table. Because of that, I

ivymasterman's avatar
ivymasterman's avatar ivymasterm...General
22

Query the json file

Dear all , I have a json file that I need to query and show following staff: Count the total number of orders? Count the number of orders that were FREE? Count the number of orders that were placed in GBP? I have loaded the file in my laravel app but I have no idea how to query the file. this is the first row of the JSON file { "created_at": "2016-01-

acoustic85's avatar
acoustic85's avatar SinnbeckLaravel
11

Check available cars query

Hi, I need a little help regarding building a query that goes and check what cars are available for set dates. This is how I have it setup, I have a Car model and migration and a Booking model and migration. Booking belongsToMany Cars through a pivot table. Then I have a function inside my controller were I'm collecting the booking criteria from the session, finding the cars an

hjortur17's avatar
hjortur17's avatar hjortur17Eloquent
4

how to make 7 join into eloquent query

hello all , I have this query SELECT scenarios.name, scenarios.id, applications.name FROM users INNER JOIN tokens_assign ON tokens_assign.user_id = users.id INNER JOIN tokens ON tokens_assign.token_id = tokens.id INNER JOIN application_tokens ON application_tokens.token_id = tokens.id INNER JOIN applications ON applications.id = application_tokens.app_id INNER JOIN scen

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

How I can make a query with 2 joins

I want to know how I can make a query with this structure in Query Builder SELECT FROM INNER JOIN (SELECT UPPER( CONCAT( ' ', ' ', ) ) AS FROM INNER JOIN INNER JOIN ) AS inner join left join ( SELECT count(*) AS FROM INNER JOIN WHERE GRO

PlasticHeart's avatar
PlasticHeart's avatar PlasticHea...Laravel
3

Query onOfMany

I want to build a query and for now i have no clue how to go about it, maybe someone here can give me a hint? I have a user and a posts model. So a user has many posts. Now i want to query all posts but only the latest one per user and only posts not older than 7 days.

mc_gee's avatar
mc_gee's avatar tykusEloquent
5

without count how to query for understating is item duplicate?

how to i very fast way to query is item duplicate?

ga46's avatar
ga46's avatar lbecketEloquent
6

Problem with DB query self referencing

Hi all, I cannot remember how to self reference a table with a DB query(and not an eloquent model). The goal is that the user who owns the ads from the AD_USER table sees all his ads in a table while also outputting in the same table the manager name and surname who takes care of his account. But because this will be an ADMIN view, the admin must be able to see all the ads and

LaraBABA's avatar
LaraBABA's avatar User476820Laravel
2

Eloquent query with filter and search

I want my search query to filter the results via dropdown and search input. Somehow the filter does't apply to my query. Maybe someone can help me out? use WithPagination; public $status; public $search; protected $listeners = ['refreshComponent' => '$refresh']; protected $queryString = ['search', 'status' => ['except' => '']]; public function se

lara28580's avatar
lara28580's avatar ZohaibAhme...Laravel
5

Laravel Query

I have to make a query that does the following: I have a json field casted as array, I want to count the number of values inside this array and compare it with an input. Let's say user inputs 3 and in the db we have {'item1','item2','item3'}, it should return true

Kris01's avatar
Kris01's avatar tykusEloquent
4

How to make query correctly?

I need to make selection from Users table with many condition i receive from API. I can receive data like: money_less money_adv level_less level_adv etc.. If i receive money_less and money_adv i make request like this: User:: ->when($request->input('money_less'), function ($query) { $query->where('money', '>', request()->input('money_less')); }) ->when($

aurorame's avatar
aurorame's avatar psrzLaravel
3

How can order by database query from lower value to greater value in laravel

I make query orderby from lower value to greater value. Although I orderBy ASC this is not get as require Price Amount Total 1 4 3 10 4 3 2 2 1 5 2 1 5 4 3 9 4 3 I think it need to adjust in double or triple digit when 1, 2, 3, 4, 5, / it make correct ascending but when two digit enter , cannot ascending order. 1 4 3 10 4 3 13 4 3 2 2 1 5 2 1 5 4 3

johndoee's avatar
johndoee's avatar Tray2Laravel
4

how to select from subquery in laravel using eloquent or query builder?

I am building one query i.e SELECT txnDate.month, txnDate.transactionDate, txnDate.dateType, userTxn.user_id AS userId, userTxn.closing_bal AS closingBalance, userTxn.opening_bal AS openingBalance FROM ( (SELECT date_format(res_dt, '%M-%y') AS month, MIN

rohansinghrawat's avatar
rohansinghrawat's avatar Tray2Eloquent
7

I want to do a when case in an2 Eloquent query.

Hi everyone. I have used the when() for checking if some variables exists, but I want to know if I can use the when() to check if a property of a column of the relation is one thing or another. Something like that: $dogshows = Dogshow::with('country')->with('club.breeds')->with('thumb')->with('card') ->with('shows') ->where('active', 1) -&

javitrujillo's avatar
javitrujillo's avatar tykusLaravel
2

Query not working in laravel query builder

i created a query in query builder app and returning result like i want SELECT answers.name, COUNT(CASE WHEN survey.village_id = 3272010006 THEN 1 ELSE NULL END ) AS result, FROM results LEFT JOIN survey ON results.survey_number = survey.survey_number LEFT JOIN answers ON results.answer_id = answers.id WHERE results.question_id = 1 AND results.answer_id IS NOT NULL AND

user154's avatar
user154's avatar SinnbeckEloquent
3

How to change path and query string when using LengthAwarePaginator?

I created a custom LengthAwarePaginator using this answer from SO: https://stackoverflow.com/a/63392687/19815685 But there were 2 issues: first the path was not correct, but I was able to change that by hardcoding the URL like so: $path = 'my-path'; $options = ['path' => $path]; return new LengthAwarePaginator($items->forPage($page, $perPage), $items->count(), $perPage

Ligonsker's avatar
Ligonsker's avatar Tray2Code Review
19

Oracle Return Column Names of Raw Query

Currently I have a code which grab $data = DB::table('queryTable') ->select( 'querysql') ->where('queryTable_key', '=' , $id) ->first(); $sqlQuery = $data->querysql; sample $sqlQuery="SELECT CD.FISCAL_YEAR, CD.PROG, FCI.DT_EXPD FROM COMDATA CD, FASTCI FCI WHERE CD.FISCAL_YEAR = FCI.FISCAL_YEAR ORDER BY CD.FISCAL_YEAR, CD.ID; How

johnw65's avatar
johnw65's avatar johnw65Laravel
4

Pagination issue with Spatie Laravel tags (not returning original query)

I'm looking for help with pagination when using Spatie Laravel-Tags, please. Here's the setup… I've coded up a really simple form which has multiple options for types of tags, so there's a select menu with options for types of post(for example, 'company news' and 'industry news') and then there's another for 'Author' etc (I'm trying to make it flexible so I can add more filter

Devedge's avatar
Devedge's avatar SinnbeckCode Review
3

How to use paginate on raw sql query?

I have a raw sql query: $result = DB::select(DB::raw(' . . . ')); But then when I try to use paginate on it I get this error: Call to a member function paginate() on array Is there a way to make paginate work on this raw query which returns an array? thanks

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
20

Is it possible to use the following query without raw sql?

I need to get distinct values by one column and also the oldest date. This is the raw sql: SELECT some_col, min(date) FROM table_name GROUP BY some_col Assuming table_name is also the same as the eloquent model name just capitalized, is it possible to make this query with either eloquent or query builder? For example I tried: DB::table('my_table')->select('some_col', 'date'

Ligonsker's avatar
Ligonsker's avatar LigonskerCode Review
21

Query last 5 entries of relationship

Hi all, let me give you some context of my problem. I need to query for conversations who are older than two days, have 20+ messages, and where at least one of the last five messages has to be sent by a real user. Currently my query looks like this: Conversation::where('created_at', '<', now()->subDays(2)) ->withCount('messages') ->having('me

coa989's avatar
coa989's avatar coa989Eloquent
0

Laravel Eloquent Query Optimization

How to optimize this query? Is this a good query or not? if not how to optimize it? return $query->whereHas('orderItem', function ($query) { return $query->whereHas('order', function ($query) { return $query->whereHas('context', function ($query) { return $query->where('doctor_id', $this->doctorId); });

FarhadMohammadi's avatar
FarhadMohammadi's avatar Tray2Eloquent
3

Eloquent collection- add extra custom parameter with custom query

I have a products table. Would like to attach related product IDs with an extra collection attr related_ids, which will will a custom query (maybe?). So far I have this- $product = Product::where('id', $id)->first()->toArray(); $related_products = Product::where('id', '!=', $id)->where('category_id', $product['category_id'])->inRandomOrder()->limit(6)->

rpmcmurphy's avatar
rpmcmurphy's avatar rpmcmurphyEloquent
2

single query with multiple filter to multiple variables.

Hello All, I am trying fetch all tests which are scheduled today and tomorrow so i have query like below, $datetime = new DateTime('tomorrow'); $tomorrow = $datetime->format('Y-m-d'); $today = date('Y-m-d'); $todayTests = Test::join('courses','courses.course_id','=','tests.course')->where('tests.test_active',1)->where('tests.test_schedule_date',$today)->select('t

Deekshith's avatar
Deekshith's avatar tykusLaravel
3

Multiple Chained "when" Methods in Eloquent Query

I'm trying to use multiple whens depending on whether a filter has values or not. But it's not working auite as I expected. If there's no search then it will filter by type (if there is one) but not if there is a search. Anybody fancy helping me out? I'll be your best buddy! User::query() ->when($filter->search, function ($query, $value) { $que

normykinz's avatar
normykinz's avatar SinnbeckEloquent
3

Speed up query

Hi i need to speed up following query any idea i have million records and do not want to use limit indexing is set for both tables eager loading is the option for services_types but do not want to send data extra key in json app already using following json format any idea please UserServices::join('services_types', 'user_services.service_type_id', '=', 'services_types.id')->

ZahidGopang's avatar
ZahidGopang's avatar ZahidGopan...Laravel
0

How to write a query to view all parent of all subcategories

suppose I have 100 sub categories and every subcategory has a parent ( suppose 10 parent categories). so now I try to get all subcategories in one array and another array gets all subcategories and all parent category in the same query is it possible?

ga46's avatar
ga46's avatar SinnbeckEloquent
8

eloquent whereHas query variation

Hello everyone, These 2 snippets below seem to achieve the same thing. Which one do you think has the proper syntax and why? 1: $user = User::whereHas('assets', function ($query) { $query->where('assets.id', request()->query('asset_id')); })->first(); 2: $user = User::whereHas('assets', function ($query) { $query->where('asset_id', request()->query('as

PKarag's avatar
PKarag's avatar SinnbeckEloquent
6