Convert Custom Query in Laravel

select * from employeedetails as ed LEFT JOIN (SELECT * FROM `punchreports` where date="2018-12-03") as pr on ed.emp_id = pr.emp_id where ed.status="Active" Can I convert this query in Eloquent ? or How to execute this query in Laravel Controller?

kshitizmittal's avatar
kshitizmittal's avatar Palak27Laravel
1

Laravel Query builder

Hello Developers , i am new to Laravel, and learning a lot from laracast and as well as from u guy... i have a question regarding laravel query , i have a statement in sql , and i want to write it in laravel, but no idea from where to start and how? can anybody help me, for an idea , i will be really appreciate ur effort. here is my sql query: ´´´

JayDee84's avatar
JayDee84's avatar birrogerLaravel
4

Complex raw query

Hi! I can't seem to get this query working in Laravel. The fromRaw() method is not documented anywhere. The code however works fine in my local SQL editor. What methods am I best off using? Or should I not be wanting to run this at all in Laravel? I am planning to run it daily from a Command to assess rankings. SELECT c.location_id, ROUND(((@rank - rank) / @rank), 2) AS pe

axtg's avatar
axtg's avatar jlrdwEloquent
3

Paginate a Laravel query with optional parameters?

I'm trying to make a search table with criteria for people to look up - name, surname, ID, course, etc. When they put in their specific values, laravel will paginate the results in a table and show them. Sounds straightforward, right? The trick is that every field is optional. So if a field is included, it must be searched based on that value. If a field is left blank, then th

uccdev's avatar
uccdev's avatar realrandya...Laravel
4

Problem query eloquent

I have made this query in mysql, to consult two tables and it works well. SELECT COUNT(nombre), usuario_ad, name, last_name FROM usuarios, users where usuario_ad=username GROUP BY usuario_ad; A member of this forum helped me with one, but I can not find how to adapt it for two tables. public function reportes(Request $request) { $request->user()->authorizeRoles(['admin'])

daniel21gt's avatar
daniel21gt's avatar daniel21gtEloquent
2

A Nested query

I have a Category / Products / Users I need to get all Categories of All Products for a specific user. I was able to make selecting a product for a specific User but I couldn't tell how to get all the categories of these products. Products and Users relationship is ManyToMany. The relationship between product belongsTo Category and Category hasMany Products Here is what I made:

ehabafia's avatar
ehabafia's avatar ehabafiaEloquent
8

Search query issue

Hi. I'm trying to search users using scopes but when trying to search for all, it only returns the wrong data. I.e I have in the searches form a checkbox with "All" "Active" "in-Active" but if I choose all it returns only the active for some reason. // Controller $users = User::role('Customer') ->findMailCentre($request->get

skoobi's avatar
skoobi's avatar skoobiLaravel
2

Problems with translation of query to eloquen

How about, I tried to translate this query sql to eloquen, but I do not achieve it, does anyone know how I can do it? SELECT COUNT(nombre), usuario_ad FROM usuarios GROUP BY usuario_ad ORDER BY COUNT(nombre) desc;

daniel21gt's avatar
daniel21gt's avatar daniel21gtLaravel
2

Query to Relationship

I have 2 Models, User and Project... A User has a Project but it can invite other Users to work in this Project... How I do query all my projects, the ones I own and the ones I have been assigned. User::find(1)->projects()->get();

socieboy's avatar
socieboy's avatar realrandya...Eloquent
5

Query a many to many relationship table

Good afternoon, Basically I have 2 models related with many to many. Species that might or might not be present on differents zones of different maps. Table especies id class Especie extends Model { public function zona() { return $this->belongsToMany('App\Zona', 'especie_zona', 'especie_id', 'zona_id'); } } Table zonas id name mapa_id class Zona ext

Penaf's avatar
Penaf's avatar PenafEloquent
2

Select Query Posts with Likes

Hello , I have a question , I am developing a website like a facebook . It will have POSTS and a User can LIKE those posts , So a user can search all those posts , if this post is LIKED ( this user has given a like ) , so it has to show that this post is liked . The like and POST is related by Many to MAny relation ship . Will be like that - POST MODEL // LIKE public funct

murilo's avatar
murilo's avatar muriloEloquent
7

Adding Columns of row in Query Builder

I need to create a query to sum items in a row in query builder. Here is my current query in laravel $SOMASRegistrations = \App\SOMASRegistration::where('CourseID', '=', $course_id) ->join('somascourseregistrationstatus', 'somascourseregistrations.StatusID', '=', 'somascourseregistrationstatus.id') ->get(); I need to add three columns within the query

jhutto's avatar
jhutto's avatar jhuttoLaravel
2

Get query strings from previous URL

Is there a way to get a list of query strings from previous URL?

johnwaweru's avatar
johnwaweru's avatar MThomasGeneral
3

Illuminate\Database\Eloquent\ModelNotFoundException: No query results for model [App\Models\Model_name] inside laravel JOB

I am trying to dump data from csv to database. But, I am getting an error as ModelNotFound inside laravel job. Although I have already imported model. Error Illuminate\Database\Eloquent\ModelNotFoundException: No query results for model [App\Models\Colorado_voter]. in D:\wamp64\www\kabza\vendor\laravel\framework\src\Illuminate\Database\Eloquent\Builder.php:414 Stack trace: #0 D

ssquare's avatar
ssquare's avatar ssquareLaravel
4

PHP Query

Hello, This is PHP. Help me understand this code: if($selectDB == true){ $query = "INSERT INTO recruitment (ID, nama, jenis_kelamin, tanggal_lahir, nohp, email, alamat, provinsi, kota, zip, status_nikah, jumlah_tanggungan, agama, file_attachment, cover_letter) VALUES (NULL, '$nama', '$jenis_kelamin', '$tanggal_lahir', '$nohp', '$email', '$alamat'

davy_yg's avatar
davy_yg's avatar lostdreame...PHP
8

Laravel advance search query

Hey guys, need some more advice again. Trying to create a search function in my app. Currently, I have the search working that it matches the search query against a model and its relationships design bringing up records that have that text in the columns. But I want the search to work also where the search query is design photography where it brings up records matching both ter

EsO's avatar
EsO's avatar jlrdwLaravel
1

How To Concat Or Insert Special Characters On To Query

So, I am looking for a way that I can insert special characters into my query such as & or a ","(comma) when it is returned Currently when I do my query like this $clientName = Client::query() ->select('clients.first_name', 'clients.last_name', 'clients.spouse_first_name' , DB::raw("CONCAT(clients.last_name,' ',clients.first_name,' ',c

boldstar's avatar
boldstar's avatar boldstarLaravel
2

Query failure in laravel, but executes perfectly in the db cli?

What am I missing here? This has to be something so silly and for some reason it's just not registering. DB query directly in the mysql cli (which is working perfectly and producing the results I want): SELECT AVG(TIMESTAMPDIFF(MINUTE, `on_scene_time`, `clear_time`)) FROM(SELECT `on_scene_time`, `clear_time` FROM `volunteer_dispatches`) temp Laravel code to bring that query to

brbourdo's avatar
brbourdo's avatar brbourdoLaravel
2

Laravel Query

How can i convert same mysql query into laravel query select max(tbl_master_vehicle_specifications_temp_conf.id), tbl_master_vehicle_specifications_sheet.id as bid, tbl_master_vehicle_specifications_sheet.field_title as ft from tbl_master_vehicle_specifications_temp_conf, tbl_master_vehicle_specifications_sheet where tbl_master_vehicle_specifications_temp_conf.primary_id= tbl_m

nishant_nayan's avatar
nishant_nayan's avatar nishant_na...Laravel
4

Laravel Query

i've mysql query how can i convert it into laravel query Ex:- select max(tbl_master_vehicle_specifications_temp_conf.id), tbl_master_vehicle_specifications_sheet.id as bid, tbl_master_vehicle_specifications_sheet.field_title as ft from tbl_master_vehicle_specifications_temp_conf, tbl_master_vehicle_specifications_sheet where tbl_master_vehicle_specifications_temp_conf.primary_i

nishant_nayan's avatar
nishant_nayan's avatar nishant_na...Eloquent
0

Creating collection wiith relationships (nesting) from 2 query builder queries

I'm trying to create a collection from 2 query builder results. These execute correctly and return as expected, i'm now just trying to transform them into how I need. $bid = collect(DB::connection('sqlsrv')->table('Bids')->select('*')->where('crm_ref', '=', $sn)->get()); $crm = collect(DB::connection('sqlsrv_crm')->table('OpportunityBase')->sel

danhorton's avatar
danhorton's avatar danhortonLaravel
0

Printing raw SQL string formed by Query Builder

Hello. I'm trying to understand how query builder is forming SQL requests, messing with DB::selectRaw(...)->where(...)->whereRaw(...) ; and joinSub(..) (subqueries) I'm trying to do less Raw inputs as possible, but to do so in proper way i have to understand how Query builder is forming it's queries: when it adds extra symbols/words and where. IMHO, the best way to unders

Aktheon's avatar
Aktheon's avatar AktheonLaravel
2

Nested query using a relationship column

Hello guys, I have this default query $products = Product::with('category', 'skus'); after some conditions i am extending my query like this $search = "SOMETHING" $products->where(function ($q) use ($search) { $q->where("name", "like", "%$search%") ->orWhere("price", "like"

kari's avatar
kari's avatar kariEloquent
2

Multiple things to one query

Looking for some advice / proper way to handle doing multiple things to one query so, for example, lets say I have a table called Events on the dashboard I need to do two things I need to first count the total number of events so I can display that to my view but I also need to only display these events that are coming up not the past dates on the same controller. which would o

kennybjr87's avatar
kennybjr87's avatar kennybjr87Eloquent
3

Sorting relations of a relation in an Eloquent query?

I have this, and it works fine: $full_categories = \App\Category::where('published', true) ->with(['panels' => function ($query) { $query->where('published', '=', true);}, 'panels.reasons','panels.tests']) ->get()->toJson(); How do I go about sorting the panels.reasons and panels.tests? In this query only for now. Is it also possible to set a defau

geerizzle's avatar
geerizzle's avatar geerizzleLaravel
2

Query bug workaround - the correct way to do it? ( \PDO::ATTR_EMULATE_PREPARES )

Hello, I have a query that on my live server (but not on the test server - yeah that's confusing) throws a "General error: 1615 Prepared statement needs to be re-prepared" after some Googling people seem to get around this by adding this to config/database.php 'options' => [ \PDO::ATTR_EMULATE_PREPARES => true ] This however brings some some [

Axeia's avatar
Axeia's avatar D9705996Laravel
1

Search query does not persist in button action

View file below <form action="/browse?search={{$listing->isbn13}}" method="GET"> <input class="btn btn-link btn-success btn-xs" id="reusable_button" type="submit" value="{{$listingButtonName}}" name=""> </form> On chrome using inspect element

biniyam20's avatar
biniyam20's avatar CronixLaravel
7

How can I use Nova's Resource Index Query in combination with a Policy?

Hey there, I have a problem that I think is somewhat of an edge case, but could be an interesting use case for some people. I have two models, programs and accounts. A program can have many accounts, and an account can belong to many programs. My issue arises within Nova when dealing with policies and the indexQuery function for resources. I don't want users to be able to see

dgoetz's avatar
dgoetz's avatar dgoetzNova
0

Need help with big query for family tree

I am trying to build a family tree from a single table persons. I have this SQL query that works fine when I use it directly in phpmyadmin: SELECT DISTINCT persons.name, father.name, mother.name, fathersfather.name, fathersmother.name, mothersfather.name, mothersmother.name, fathersfathersfather.name, fathersfathersmother.name, fathersmothersfather.name, fathersmothersmother.n

Tommy001's avatar
Tommy001's avatar lostdreame...Code Review
6

query json column

I have an column like this: "data": "[{"value":"0"},{"value":"3"}]" column name data with json data, how can i query json data in orWhere query I have something like this that doesn't work, i have tried may things no success, what im doing wrong ? orWhereRaw('JSON_CONTAINS(data->"$.value", \'["0"

petritr's avatar
petritr's avatar petritrLaravel
1

Help me build this query

Hi! Sorry for the boring question, but I'm having trouble building this query in Eloquent. I have a product with several skus. Each sku has many attributes. Each attribute has a type which could be something like "size" or "color", and a value, which would be something like "M", or "black". Each product has an arbitrary number of attribut

thorlucas's avatar
thorlucas's avatar humbertopi...Eloquent
1

doesntHave with orWhere in query

I need to build query in which I will get projects with tasks that: (current user belongs to project && project has tasks that are assigned to current user) or (project has tasks that are not assigned to anyone). I stuck with this query: $projects = Project ::with('tasks.status', 'tasks.users') // this should be first conditio

wizjo's avatar
wizjo's avatar wizjoEloquent
2

Strange Error with Laravel DB query

I really hope somebody on here could help me with this very strange issue. I have an Laravel DB query which runs absolutely fine on my development host, however I get an error when I deploy it to my UAT host. [EDIT - simplified example] SQLSTATE[42S22]: Column not found: 1054 Unknown column 'c.xxx' in 'order clause' (SQL: SELECT c.* FROM (SELECT 0 AS xxx, u.* FROM users u) c O

rezamk's avatar
rezamk's avatar heikokrebsLaravel
10

Signed urls with dynamic query params

We have a signed url which for downloading a report as pdf. Know it would be great, if we can provide a filter as query parameters like ?state=open or ?limit=200 to this signed url (without generating this url for each variation of the filter). Can we configure this signed route to just verify the url without query params? Or is there a better way to restrict file download for

niseku's avatar
niseku's avatar D9705996Laravel
1

How To Query Entire Model For Value

So I am wondering if there is a way to query a model for values but in a way that it checks the entire model instead of a certain column. For instance if I use $client = Client::where('first_name', $request->keyword)->with('engagements')->get(); return response()->json($client); It will return any record where the first_name column exactly matches the give

boldstar's avatar
boldstar's avatar boldstarLaravel
19

Problem accessing query parameters in middleware

I am playing with middleware and trying to make my own. I want a middleware that returns a message "You must say the magic word" if a query parameter 'please' is not present. I think I have the right logic. It works if 'please' is present, but throws an exception is please is not present: Trying to get property 'headers' of non-object in the VerifyCsrfToken.php class

aliam13's avatar
aliam13's avatar aliam13Tips
1

How to fix No query results for model [App\User] settingedit in Laravel 5.6?

working with Laravel 5.6 and mysql. in My application i am going to show for edit current loging user name and email. this is User Model public function scopeUserr($query) { return $query->where('name',Auth::user()->name); } and UserController public function settingedit() { $users = User::userr()->get(); return view('users.settingedit')->withUsers($u

Sunpower's avatar
Sunpower's avatar SunpowerLaravel
10

Eloquent sync() method does separate insert\update query for each item

When i try to update several items in pivot table, using sync(), each insert\update is done with a separate sql query. Is there any way to make it work as a single query, without adding additional logic? $skills = [1 => ['experience' => 5], 2 => ['experience' => 7]]; $user->skills()->sync($skills); produces 2 queries instead of one combined: insert into user_

Art's avatar
Art's avatar ArtEloquent
2

Query takes too much time in loading

i made a query for random product my query Looks like SELECT * FROM product AS a ORDER BY RAND(CURDATE() + INTERVAL (SELECT MINUTE(NOW())) MINUTE - INTERVAL 1 MINUTE) LIMIT 15 issue is i have 150K data in my product table the query takes woo much time to load maximum 9 second What should i do for fast loading Please Help

hasaftab's avatar
hasaftab's avatar hasaftabLaravel
2

Insert multiple rows and return all their ids in one sql query (Psql only)

I need a way to insert multiple rows into sql table, and return all inserted primary key ids with one sql query. A working raw sql query (which i tested in psql) looks like this: insert into technologies (name, user_created) values ('test 1', 4),('test_2', 4) returning id In docs i only found method allowing to insert only one row and return its id: DB::table('technologies')-&g

Art's avatar
Art's avatar rip057Laravel
6

Query Builder Issue

Hey, I'm trying to build a small query module and have hit an issue. I read this article and have implemented something similar where I'm trying to search through a related model using Builder: public function filter(Request $request, City $city) { $city = $city->newQuery(); // Search for a city based on its state. if ($request->has('state'))

CodeFox's avatar
CodeFox's avatar tykusLaravel
3

Write complex query with Eloquent

Is possible to do this with query builder: $query = "SELECT * FROM products WHERE status = 1"; some if statement and concat $query .= "AND product_price BETWEEN $request->minimum AND $request->maximum"; again some if and $brand_filter = implode("','",$request->brand); $query .= "AND product_brand IN('". $brand_filter."

adnan483's avatar
adnan483's avatar adnan483Eloquent
4

Check if Eloquent query returned anything

When I use the following query: $user_id = auth()->user()->id; $profiles = Profile::where('user_id', '=' ,$user_id)->get(); It always returns this when I use print_r to check: Illuminate\Database\Eloquent\Collection Object ( [items:protected] => Array ( ) ) This means that I cannot use count, because it will always return true. @if(count($profiles)) not empty

rpm192's avatar
rpm192's avatar rpm192Laravel
1

I am calling query in every single function for navbar

I am calling query in every single function for navbar public function home() { $navbar = Course::all(); $class = Class_::where('is_delete',0)->get(); return view('frontend.home',compact('class','navbar')); } public function cart_list() { $navbar = Course::all(); return view('frontend.cart_list',compact('navbar')); }

amk's avatar
amk's avatar tykusLaravel
4

Decrypting data in Eloquent Query

Hello there, I have some data in my database that I would like to encrypt/decrypt when I make eloquent calls. I added this encryptable trait from this discussion and it works fine when I use dd(Client::first()->mobile) https://laracasts.com/discuss/channels/laravel/encrypting-model-data However I'm trying to pass this data to some DataTables on the front-end using yajra/lara

matttonks11's avatar
matttonks11's avatar marboboLaravel
12

Eloquent, Query Builder or other way?

Eloquent, Query Builder or other way? Hi to all! I want to present a tablet with clients and the “number” of related records, in this case number of orders from CONTACTS table. In php I had can resolve this in two ways! FIRST WAY Make a simple join select with count like this: SELECT clientes.*, Count(clientes_contactos.cliente_id) AS ncontactos FROM clientes LEFT JOIN clientes

Amaro's avatar
Amaro's avatar AmaroLaravel
12

Global search custom query for one resource

How can i customize the search query for one nova resource?

devOps92's avatar
devOps92's avatar devOps92Nova
2

Eloquent needs much time than phpmyadmin sql query

hello , first I added to mysql ST_Distance_Sphere() function witch calculate the distance between two coordinates (lat,lon) I created one controller to return the result as json response : public function index( $from_location) { $response = station::select('id','name','label','address','longitude','latitude') ->whereRaw('ST_Distance_Sphere( POINT(la

GodziLaravel's avatar
GodziLaravel's avatar mostafalar...Eloquent
5

How optimize that query?

create a form where I must, make a dynamic , and for that I execute this query that returns the data, but I think I could optimize this query with eloquent, some idea: Brand::join("customers", "customers.fk_uid", "brands.fk_uid") ->where("brands.fk_uid", $request->id) ->pluck('brands.brand','brands.id')->all();

jok3r's avatar
jok3r's avatar jok3rEloquent
3

Help Optimising Query

Hello, I need some help optimising a query, and putting the correct syntax in to blade. At the moment, I am generating 12 SQL queries. I know 6 of them, come from blade, but the other 6, I am a bit lost on. public function index() { $products = Product::with('media')->enabled()->promote()->inRandomOrder()->take(6) ->

elliotk's avatar
elliotk's avatar elliotkLaravel
7