Need help with SQL query - stuck since days

Hello, My question is more about a SQL query, rather than Laravel. There is a table called ´item_meta´ which has a foreign key `fk_i_item_ and a field `s_val. And a table called t_user which has the primary key pk_i_id , and a field s_phone_mobile And a table called t_item which has the primary key pk_i_id, a foreign key fk_i_user_id So, t_user has many t_item and an t_item has

codemode's avatar
codemode's avatar codemodeGeneral
9

How to get name and surname by foreign key and add it as new value to current query

Hi, I know that title is not very well described, here's what I'm trying to do, I have quite common eloquent call: $artworks = Artwork::orderBy("created_at", "asc")->paginate(12); artwork has id, title, year, price, and 'author_id' columns, 'author_id' is foreign_key to table with author names, surnames etc. now with above query I get info about artwork

Pauliuso's avatar
Pauliuso's avatar PauliusoEloquent
6

Is there no built-in query caching in Laravel?

I saw in the docs in Laravel 4.x there was a remember() method to cache DB queries. I don't seem to be able to find it in Laravel 5.x. I have found an external packages like: https://github.com/dwightwatson/rememberable It would also be easy enough to write in some query caching using Laravel's Cache class, but I just want to make sure I am not missing anything. Is there no Cor

mjlovefl's avatar
mjlovefl's avatar mjloveflLaravel
0

Is there a method like toSql() to see the raw sql query with the parameters mapped automatically.

I am doing test to see whether the query binding what I have written is accurate. Currently I am using toSql() method which outputs the raw sql with ? as the value supplied to conditions. But sometimes to make the test process faster I would have preferred to use a method that would return the sql string with values binding done automatically. Instead of expecting select name f

COACHTHEM's avatar
COACHTHEM's avatar ModestasVEloquent
2

Insert null value with query builder

Hi, I've this query: $id = DB::table('table1')->insertGetId([ 'id' => $request->input('id'), 'idExternal' => NULL ]); The query executed is like this one: INSERT into 'table1' ('id', 'idExternal') values ('1', '') The idExternal is a foreing key so, I can't write an empty value (like '') but I need to write NULL (the column is nullable). Why the

stepassia's avatar
stepassia's avatar CosminComa...Laravel
10

Why does updateGetId not exists on the query builder?

Hi, I know insertGetId(); exists on the query builder. But why is there no updateGetId();? Is there a reason for this or should I make a pull request because I need it ! :) https://laravel.com/api/5.3/Illuminate/Database/Query/Builder.html Thanks!

Lars-Janssen's avatar
Lars-Janssen's avatar Lars-Janss...Laravel
4

Eloquent polymorphic return zero but query works in MySql

A difficult question to ask and even harder to find answers for. When I dump the query out to the screen and run in directly in MySql I get the desired results. However executing the code in the project produces zero models. Migrations Schema::create('part_base_parts', function (Blueprint $table) { $table->increments('id')->index('part_base_parts_id'); $table-&g

jandk4014's avatar
jandk4014's avatar jandk4014Eloquent
1

Laravel DB query builder with LEFT JOIN using an RAW expression

I would like to left join data from a many-to-many relationship. In my first attempt to solve the problem I ended up with the situation described at https://stackoverflow.com/questions/916414/how-can-a-left-outer-join-return-more-records-than-exist-in-the-left-table Now I thought that I concatenate the values from the many-to-many relationship before I do the left join, but I'

akogler's avatar
akogler's avatar akoglerLaravel
0

No query results for model [App\Partais].

hello, i need help, error problems No query results for model.. https://pastebin.com/EXnvWbrj

Dickyperdian's avatar
Dickyperdian's avatar tykusLaravel
1

Ensure only one single record is returned by Eloquent query

I checked Laravel collection methods and can't understand - is there a method for Eloquent query to ensure that exactly 1 record is returned - not 0 and not more than 1? Something like singleOrFail(). As an example, in Entity Framework there is First() which works like Laravel's firstOrFail() and there is also Single(). Or, is it not implemented (yet)?

ownmaster's avatar
ownmaster's avatar jjudgeEloquent
6

Need help with simple query builder code

Maybe it's late and I am missing something obvious, but I am very confused. Can someone explain to me why this code $products = Product::where('id',15); $result = $products->get(); dd($result); return 1 (correct) result and this code $products = Product::where('id',15); $a = $products->where('id',1111); $result = $produc

Torch's avatar
Torch's avatar andreasbak...Laravel
7

On all page, i have a topmenu that needs to query the database... where should i place my Eloquent code

Hello to all, I'm creating a new website and i have a question about my top menu.... In my old website, i included a php page topmenu.php in my php template...... and in this top menu.... if user is logged, i query the database to check for new messages... or if not logged, i show register and login button.. But now... in Laravel... i create a template (main.blade.php) and i in

pascal83's avatar
pascal83's avatar pascal83Eloquent
2

query builder

select DISTINCT id,file_no,registration_date_ad,registration_date_bs from building this is my query. i have databse with table building.i want to get the specified fields data in view page in table form. please help me with controller and blade.php

devamit2018's avatar
devamit2018's avatar muhammadja...Laravel
4

When creating relations with the Laravel ORM, what is the raw MySQL query behind it?

For example, if I have a Posts and a Comments model and "bind" (not sure if it's the proper term) comments to a post with... class Post { public function comments() { return $this->hasMany('App\Comment'); } } What is the raw MySQL query behind that? I'm kind of weak on MySQL so I just want to know to learn more about it and how should I struc

p0t4t0's avatar
p0t4t0's avatar SnapeyLaravel
4

Database Query Builder for Objects.

Using database query builder (https://laravel.com/docs/5.5/queries) get() returns me a collection of StdClass. I am using a postgresql database. Is it possible somehow to create objects from my tables and then return a collection of objects? i.e. convert the stdClass to the specific Object representing the table? Maybe a different library? I would rather not create my own class

truetaurus's avatar
truetaurus's avatar martinbeanLaravel
1

Search in JSON column does not work with Query Builder and Eloquent Model (but works with raw query)

Hi, in Laravel 5.3, I'm trying to use that short syntax for the JSON columns: $allLeadsEloquent = LeadsCache::where('json->linked_company_id', $companyId) ->get(); The result: empty collection. Note: the same result is with Query Builder (DB('table_name')). Now. I open Debugbar and grab the "Raw query" produced by this code and run this query in the Sequel

pilat's avatar
pilat's avatar pilatEloquent
1

Need clarification on query strings with Laravel

How should one handle query strings with Laravel? Is this fine? If it is then are there better or more ways to do it? What instances is it better to use it over "pretty" URLs? Route::post('/comment?pid={post}', 'CommentController@store');

p0t4t0's avatar
p0t4t0's avatar jlrdwLaravel
5

Is there a way to extend the Query Builder?

I would like to extend the Query Builder so i have my own raw Query builder class, that i can filter down. I tryd to do it like this: namespace App\Query; use Illuminate\Database\ConnectionInterface; use Illuminate\Database\Query\Builder; use Illuminate\Database\Query\Grammars\Grammar; use Illuminate\Database\Query\Processors\Processor; class Contract extends Builder { publi

Tomi's avatar
Tomi's avatar martinbeanLaravel
5

Group by and order by in the same query

I need to fetch the results from a table using group by and order by in the same query. Basically I need to fetch the latest transaction from the transactions table based on two columns i.e. latest row based on unique combination of two columns. I need to fetch the latest row grouped by two columns. Eg: I have a table transactions which contains the transactions of the quotatio

itinigam's avatar
itinigam's avatar itinigamEloquent
0

LAravel SQL Optimization (Query, cache, etc)

Hi guys, I'm working on a project with a lot of data. I'm having a hard time getting a query result within 5 seconds. One table(warranty) has almost 4,000,000 rows. One example function I'm doing is a keyup function for product search function for the logged in user : Pass input data via ajax get request. Run this query: ItemWarranty::where('user_id', request()->user()->

chireen's avatar
chireen's avatar chireenLaravel
7

Converting SQL query to an Eloquent query builder.

Hello, I am pretty new to Laravel, and can't find a solution to my problem. I've made a SQL query, $likes = DB::select(" SELECT count(likes.post_id) AS AllCount, posts.id, posts.image FROM posts, likes WHERE likes.post_id = posts.id GROUP BY likes.post_id, posts.id, posts.image ORDER BY AllC

pasiutlige's avatar
pasiutlige's avatar pasiutligeLaravel
3

Undefined variable in function with query

Hi, I have problem with my function, error is: "Undefined variable: filtry". I know why, the variable $filtry have to be defined in query function (like variable $j is), then it is working. But I cannot have this variable static, because variable $filtry will be always diffrent, so I need set it before function, then call it in function. Is it possible? Thanks in adva

paklic9's avatar
paklic9's avatar paklic9General
2

Help with SQL query

Hello, I have a fairly nasty SQL query, which i'm not able to execute. So, i need to extract 2 fields for selected dates... and then the same 2 fields for just the last 6 months. I decided not to use Eloquent for the sake of ease. Given the circumstances, i need to fit all this into ONE sql query. Following is what i have right now : select customerAccount, count(id) a

codemode's avatar
codemode's avatar modi788688Laravel
2

Trying to use query builder to constrain by non-unique values in a column

This is my query so far: $units = DB::table('units') ->join('locations', 'locations.id', '=', 'units.location_id') ->join('castles', 'castles.id', '=', 'units.castle_id') ->join('unit_types', 'unit_types.id', '=', 'units.unit_type_id') ->select('units.location_id', 'units.previous_location_id', 'units.i

deja's avatar
deja's avatar dejaLumen
11

Adding unique constraints to a query

I have this query: $units = DB::table('units') ->join('locations', 'locations.id', '=', 'units.location_id') ->join('castles', 'castles.id', '=', 'units.castle_id') ->join('unit_types', 'unit_types.id', '=', 'units.unit_type_id') ->select('units.location_id', 'units.previous_location_id', 'units.id as u

deja's avatar
deja's avatar dejaLaravel
16

Help using join() with previous query

Hello: I have doubts about using the join() function of a Model when a table is the result of a previous query. I have a previous query which is in fact the result of joining two tables $query = Model1::join('table2','table1.column1','=','table2.column1'); What I want to do is joining this query with a table represented by an Eloquent model, something like: ModelA::join($quer

aperezmi's avatar
aperezmi's avatar getupkidEloquent
1

Create Query with Eloquent

I want to use laravel eloquent to implement the below query. Please help SELECT COUNT(`category_id`) as categorycount,`category_id`,`product_id` FROM `productcategories` GROUP BY `category_id` ORDER BY categorycount DESC Model Name is Productcategory

V9द's avatar
V9द's avatar vinodsharm...Eloquent
3

Not sure what kind of eloquent query to make with inbetween table

So I got the following table structure: profiles id name lastname profiles_has_specialties profile_id specialty_id level specialties id name Now I thought I would need a query like: //Inside the controller dd(Profile::with('specialties')); //The Profile model public function specialties() { return $this->hasManyThrough(Profile_has_specialty::class, Spe

killstreet's avatar
killstreet's avatar killstreetEloquent
4

Query with orWhere returns invalid elements

Hello. I have a 'Friends' table in my database and it stores ids of 2 users and accepted field ( if user confirmed invitation ). Now I want to remove specific friendship and my query is: Friend::where([ 'user_id' => $friend_id, 'friend_id' => Auth::id(), 'accepted' => 1, ])->orWhere([ 'user_id' => Auth::id()

pmieleszkiewicz's avatar
pmieleszkiewicz's avatar pm95Eloquent
3

Laravel newbie - Haversine query - SelectRaw inside a Join closure

Hi, we are trying to implement a haversine query using Laravels' join closure but it doesn't want to work. We created a dummy method to count records based on the passed data (lat, lng and radius) which worked fine. We just can't seem to get it going inside the join closure... Dummy Method public function dummyTestOne($request){ $listings = new BusinessListingsModel();

mecca6's avatar
mecca6's avatar mecca6Laravel
1

Eloquent query showing wrong result

I'm at a loss here. I have different statuses on a post: published, draft, etc. My query should only select published posts; on my development server it's working correctly, yet on my production server it's showing posts with a status of draft and I can't figure out why. $posts = Post::latest('published_at') ->whereHas('user', function ($query) { $query->where

timgavin's avatar
timgavin's avatar crnkovicEloquent
2

How to prevent ambigous on Eloquent based query?

Hi, I'm trying to figure out how to extend a builder query to join with another table and by this other table. Unfortunately both tables "articles" and "users" have a column named "lang", which results in an ambigous error. $articles = Article::published() ->lang() ->with('genre')

dillscher's avatar
dillscher's avatar dillscherEloquent
2

update query with two conditions

I want a query with two conditions to update but it should be an and conditions. my code is below. it does not work. DB::table('attendence') ->where([['name',$name] ,['type',null] ]) ->update([ 'checkedout' => $this->data->checkedout, 'type'=> $this->data->type, ]);

esperanza's avatar
esperanza's avatar ezperansaLaravel
2

Query builder

How to make this query with QueryBuilder or what? SELECT duznici_2018.*,sum(rate_2018.rata) as ukupno FROM duznici_2018 LEFT JOIN rate_2018 ON duznici_2018.duznikID=rate_2018.duznikID GROUP BY duznikID DESC I'm stuck on how to add SUM: $sumrate = DB::table('duznicis')->leftJoin('rates', 'duznicis.kupac_id', '=', 'rates.duznik_id')->get();

adnan483's avatar
adnan483's avatar YamaLaravel
2

Query is not working even though I have one almost identical working.

I am trying to build a communication page where emails can be filtered etc... I created the page and emails can be successfully filtered however, I wanted users to be able to save shortcuts to save them time filtering the same again and again. So I decided to create a comm shortcut object which stored each value of the filter and stores the array as a string by serialising it.

nizam0786's avatar
nizam0786's avatar nizam0786Eloquent
1

MySQL Query

Hi, I would like to put a condition in a query, but I do some things wrong, what is it? $id = 1; $groups = DB::table('table_example') if($id!='0'){ ->where('id', '=', $id) } ->limit(30) ->get(); Best regards

KonScyence's avatar
KonScyence's avatar KonScyenceLaravel
2

DB Query builder reports column not found, but refers to a value instead of a column title

I have the table: CREATE TABLE `project_user` ( `project_id` int(10) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `role_id` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, KEY `project_user_project_id_index` (`project_id`), KEY `project_user_user_id_index` (`user_id`), CONSTRAINT `project_user_proj

akogler's avatar
akogler's avatar ShawdowLaravel
2

Query outside database with 'between condition

I'm confused at Laravel's query DB. Here is my code: collect(DB::connection('2ndDB')->select('select avgvalue, logtime from 2ndDB.mytable where logtime =?',array('14:38'))); It does work on and return the right value. However, once I change the SQL statement with 'between' condition, down here: collect(DB::connection('2ndDB')->select('select avgvalue, logtime from 2ndDB.m

howard_hsiao's avatar
howard_hsiao's avatar howard_hsi...Eloquent
2

Active results like Eloquent "with" in query builder

Hello, I'm trying to get database records as same as Eloquent "with" so for example my query builder query return the following data $comments = \DB::table('comments as T1') ->select('T1.product_id','rating') ->where('T1.shop_name', '=', "$shop")

ahmadissa's avatar
ahmadissa's avatar sbcman74Laravel
4

how to make relationship in Eloquent with this Query Builder

product ----------------------------------------------------- product_id | product_name | ... | category_id 1 | product_1 | 1 2 | product_2 | 2 category ----------------------------------------------------- category_id | category_name 1 | type_1 2 | type_2 my expect result

ReakyMark's avatar
ReakyMark's avatar shez1983Eloquent
3

Making a Laravel 5.4 query on a JSON field containing a JSON array

Hi everyone, i'm trying to query a JSON field containing an array of values. For exemple sake we'll name the table "User" and the field "Friends". Here's how the Friends field looks like : [{ "id": 1, "img": "img-1.jpg", "name": "Name 1" }, { "id": 2, "img": "img-2

Vic-Gutt's avatar
Vic-Gutt's avatar Shahrukh4Eloquent
9

How can I convert a query where it brings a collection of objects to a find or findOrFail?

How can I convert a query where a collection of objects brings me and therefore if I want to access an attribute of that object I have to cycle through it, unlike a find that brings me a different structure and I can directly access an attribute without need of the cycle, If I know that my query will bring me just one row how can I convert the structure where to find to be able

anderking's avatar
anderking's avatar tykusEloquent
1

Change SQL query of laravel Auth with ID

Hello, I would like to use this: Auth::loginUsingId($user['uid'], true); The problem is that in the database, the column of the id is not id, but uid. How do I have to change the SQL query? Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * fromtkl_userswhereid= 1 limit 1) Thanks

KonScyence's avatar
KonScyence's avatar KonScyenceLaravel
2

Query builder generates valid query but throws error in code

I have an SQL query that queries for all unverified company owners to send out a reminder email on unsubmitted applications. The following is my query builder code: $unverified = DB::table('OnlineApp.applications AS app') ->select('own.name', 'own.email', 'usr.id', DB::raw('MAX(tok.id) as tokenId'), 'tok.token') ->join('OnlineApp.applicatio

Ixalmida's avatar
Ixalmida's avatar bobbybouwm...Eloquent
1

No query results for model

Am getting this error Any idea how to fix this? php artisan ScheduleMsg:send In Builder.php line 329: No query results for model [App\Updatacc] My Command class <?php namespace App\Console\Commands; use Illuminate\Console\Command; use Carbon\Carbon; use App\Updatacc; use Ap

diadal's avatar
diadal's avatar diadalLaravel
5

RAW Query Throws Incomplete Data

I build a query using foreach (dynamic), when I pass that query using select(DB::raw(QUERY))->toSql() the select query it returns work perfectly fine in phpmyadmin mysql query but when I use get() instead of toSql() I do not get complete data

askshinde's avatar
askshinde's avatar askshindeLaravel
4

Issue when the SQL query is slower than multiple requests

Hi. I have a page where an user can upload some photos. Imagine that the limit is ten photos per user. I use dropzone to upload multiple photos, and it runs one request per photo upload. The problem is that when I check the number of the photos inthe request, when the next request "starts", sometimes the query has not finished, so the user "sometimes" can up

luddinus's avatar
luddinus's avatar luddinusGeneral
4

Laravel | Conditional search query based on user role / status

Hi, team: I have a scenario where user records can be pulled up based on requester's "admin" role. Short of writing an if/else query such as below, is there an elegant way to deal with this? public function search(Request $request) { $this->validateSearchRequest($request); if (Auth::user()->isSuperAdmin()) { return User::where('fname', 'like', '%' .

abhigarg's avatar
abhigarg's avatar bobbybouwm...Code Review
7

Converting SQL Query into Eloqeunt

I have the following sql query I need to convert to eloquent. I have the "lat" and "lng" fields in the user table. Basically I need to select all users within the 25 mile radius. SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM marker

Loach's avatar
Loach's avatar LoachEloquent
33

Trouble in custom query

Hi! I'm having some trouble creating a query in laravel, if anyone has any tips I'd appreciate! I have the following tables: "Item", "User", "Category", "ItemUser", and "CategoryItem". An User can have multiple Items and vice-versa (belongs to Many), that's why I have the ItemUser pivot table. An User can create categories (Has

gbdematos's avatar
gbdematos's avatar JoaoPedroA...Eloquent
1