Eloquent query to count unique column

How would I query for a return count of unique column values? So if my users have an attribute of color with optional values of red, blue, and green. I want to return: red => 15 blue => 33 green => 23 I can do this easily with individual queries such as: Model::where('this', 'that')->count(); But I want to return all of them at once. Thanks.

NoTimeForCaution's avatar
NoTimeForCaution's avatar DawgOnKingEloquent
3

Querying a table and its relationship, and using the relationship to query related records

I am using Laravel 5.6 and mysql. I have a Tasks table that has a StudentID column linked to the primary key StudentID in the StudentInfo table. Each student in the StudentInfo table has a IDNumber column which is unrelated to the StudentID. I query all tasks, and also the students who were assigned to that ask. One student can have many tasks, but a task only has one student.

derrickrozay's avatar
derrickrozay's avatar derrickroz...Laravel
0

When using Homestead how do you run a query in Redis using the console?

So when I want to run a MySQL query in Homestead I just type mysql and I'm in MySQL mode where I can just run queries. How does it work for Redis? Like what do I have to do to run a query in the console window for Redis?

rotaercz's avatar
rotaercz's avatar lostdreame...Laravel
1

Why is my GET parameter 's' being replaced with 'query' in pagination?

I've a search box that makes a GET request through my /search/ route with parameter named s . So I've search urls like /search/?s=keyword1+keyword2. The problem happens when the results are paginated. I'm getting following pagination URLs /search?query=keyword1+keyword2&page=2 I've no clue why is my s parameter is getting replaced by query parameter. Does anyone have a clue

thebigk's avatar
thebigk's avatar jlrdwEloquent
1

Reuse Query

I've created this simple query. $models = Model::whereBetween('created_at', [$start, $end]) and I am trying to use it for several things. For example, I am getting the total count with $models->count() // 20` and the total amount with `$models->sum('amount') // 50 I am then trying to filter the query for each date in an array and this is where I am having problems. Run

TxNuno's avatar
TxNuno's avatar JuanRangelEloquent
1

How to write general query in laravel format

Hi, How to write following sql query in laravel select bins.organization, sum(products.price) as price, sum(products.vat) as vat from products inner join bins on products.binid = bins.binid group by products.binid When i write this as $product = Product::select('bins.organization', 'sum(products.price) AS price', 'sum(products.vat) AS vat')->join('bins','products.binid', '='

nbrshamim's avatar
nbrshamim's avatar jlrdwLaravel
3

Grouping query and then displaying on blade

Hi. Im at another learning point where i want to try and group an elequent query by user_id and date, but then have it display correctly in blade. Im completely new to grouping and ive tried a few things from the laravel docs and google, but its not quite doing what i want. So hoping the awesome laravel community can help me once again :) ... I have database table with a lot o

skoobi's avatar
skoobi's avatar skoobiLaravel
2

Laravel RAW query with IGNORE doesn't work as expected

I have a project for travel agent where I need to insert date that support incomplete date. So there are 3 possible format: YYYY-MM-DD YYYY-MM-00 YYYY-00-00 00 means month or day is not set. In my database (MariaDB v10.1.28) I can run this query and it sets the date as I wish: UPDATE IGNORE schedule SET departure_date = "2019-00-00" WHERE id = "10" // resu

ledah's avatar
ledah's avatar ledahLaravel
6

SQL query

Hi all I need to query a table with a value that is between the value of two columns e.g. $record = table_1::where([['column_1', '>=', $number],['column_2', '<=', $number]])->firstOrFail(); I have it working in Sequel Pro with this query. SELECT * FROM table_1 WHERE '111' BETWEEN column_1 AND column_2; The question is how do I write that query in Laravel? Thanks Chri

ChrisPercival's avatar
ChrisPercival's avatar SnapeyCode Review
17

Further refine a query once it hits blade

I am using moloquent (mongodb eloquent) to query a local DB to return a collection of tweets. Once I have the tweets from that query, I need the option to group them by account. I have the page set up to display each account that was present in the result and how many tweets they had. The problem I am running in to is generating the links so that when the user clicks on that ac

ldslaracasts's avatar
ldslaracasts's avatar ldslaracas...Laravel
2

How to convert sql query into laravel query builder ?

I have this sql query below, but i want to convert this into laravel query builder format SELECT u.`name`, u.`email`, uf.`flight_no`, uf.`pick_up`, uf.`arrival_date`, (SELECT name FROM guides INNER JOIN users_agency ua ON guides.`id` = ua.`guide_id` WHERE ua.`user_id` = u.`id`) as guide_name, (SELECT mobile FROM guides INNER JOIN users_agency ua ON guides.`id` = ua.`guide_id`

somenet77's avatar
somenet77's avatar webtricksh...General
4

db::select returning null at joins query

I am trying to fetch results from a joins query using DB::select( ). The query shows output in mysql phpmyadmin but not in laravel. $q1=DB::select("Select id from vehicle"); // returns a single column of multiple ids for($i=0; $i<=count($q1); $i++) { $q2=DB::select("Select * from reservation inner join vehicle on (reservation.v_id=vehicle.id) where vehicle

Haider's avatar
Haider's avatar Tray2Laravel
2

How to use select and selectRaw together query in eloquent?

Hi, I want to get all data from a model with name 'user' with a eloquent query and in this query get count of a column and sum of other column, but I won't to divide this action in 3 query... What should I do?

alihoushyaripour's avatar
alihoushyaripour's avatar bobbybouwm...Eloquent
1

How to paginate hasMany relationship in user article query

i have hasMany relationship for user articles, This is my user model public function articles() // returining correct data { return $this->hasMany(Article::class); // this get all aricles related to a user } in user controller im passing the data to the view like this public function articles($id) { $subscriber = User::find($id)->with

nhayder's avatar
nhayder's avatar nhayderEloquent
8

Update different rows with different values in one query!

Update different rows with different values in one query! Some example: $commentsOderdListData = [ ['id'=>1,'sortOrder'=> 0], ['id'=>2,'sortOrder'=> 2], ['id'=>3,'sortOrder'=> 1], ['id'=>4,'sortOrder'=> 3] ]; I know maybe something like this will work: $postID = 2; $post = $this->model->where('id', $postID )->firstOrFail(); foreach($

Ranx99's avatar
Ranx99's avatar Ranx99Eloquent
2

Query log

Does anyone know how to monitor the time of a query? I know there is a query log but my idea is guarded in bd whenever any query exceeds a certain time defined by me

filipe1221's avatar
filipe1221's avatar filipe1221Laravel
10

Scenario : in my blade as a news paper category , first item information show big view but other s item ar small show , how can i view data in blade from database? what kind of query ?need to write

how can i view data in blade from database? what kind of query need to write ?

towhid's avatar
towhid's avatar towhidLaravel
4

Help with query

Hello, Sorry I'm not excellent in SQL and I need to write a plain sql query to get me the top 5 rated places by calculating the average rating for all services. My tables PLACE Table id country_id is_active name RATING id place_id service_id rating I tried the below but it throws an error: $sql = "SELECT p.id as id, p.name as name, avg(r.rating) as rating FROM place p,

lararocks's avatar
lararocks's avatar lararocksLaravel
4

How Can I convert a simple SQL Query to Eloquent

Sorry for noob question. My teammate give me an oracle SQL query. select b.PI_NO from mer_bom_material b, new_order a where a.ORDER_NO=:p_order I need to convert this to eloquent. Here :p_order is a parameter value. Thanks

marufalom's avatar
marufalom's avatar lostdreame...Eloquent
1

Potential Vue and Laravel Routes Query String Conflict

I have an Archives Page for my articles. Each link on the archives page has a query string in its href attribute, just like Jeffrey taught us in the "Laravel 5.4 From Scratch: Archives" tutorial. On this and every other page, my Vue components (Header, Footer, Newsletter SignUp) load perfectly fine. However, when I click on one of the links, <a href="/the-jour

donfrodriguez's avatar
donfrodriguez's avatar donfrodrig...Laravel
1

Query Classes

I'm sure I've heard JW mention the idea in one of his videos (or I might have dreamed it). But the thought of distilling repositories down and SRP occurred to me today while I was mucking about with a toy project. I'd be interested in people's opinions of single query classes and the ups and downs of using them. Something like this ... class PropertyRatesInRange { public st

topvillas's avatar
topvillas's avatar CronixGeneral
1

Query fails on production but runs on local environment

Hello. The next query in successfully running on development environment, but fails on production one: \App\Company::withCount('activesales')->where('activesales_count', '>', 0)->get(); (Returning "Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'activesales_count' in 'where clause'" error). Company.php

OldEr's avatar
OldEr's avatar CronixLaravel
9

No query results for model [App\User].

I changed password bcrypt to md5. LoginController.php use App\Http\Controllers\Controller; use App\User; use Illuminate\Foundation\Auth\AuthenticatesUsers; use Illuminate\Http\Request; use Illuminate\Support\Facades\Auth; public function login(Request $request) { $user = User::where('email', $request->email) ->where('password',md5($request->password))

mozew's avatar
mozew's avatar CronixLaravel
15

query twice back

how can i query back twice? i expalin you have a users, posts and favaries tables so i am getting user info from posts table "{{ App\Model\user\user::find($post->poster_id)->name }}" so a user can add posts to his favory list which i am saving with user_id and post_id in favories table then from there using the same algorithm i will get the post info from post_i

patibra's avatar
patibra's avatar galguerraf...Laravel
1

Difficulties getting a nested query to work

Hi, I need to convert a query that is designed to find the higher price from two dimensions matrix (width/height). On SQL is a fairly trivial query: SELECT price FROM matrices INNER JOIN ( SELECT price_group_id, max_width FROM matrices WHERE price_group_id = 1

lmartins's avatar
lmartins's avatar lmartinsEloquent
2

Overloading newQuery and put the rest of the query in scope

I'm working on an application where a lot of models have a Company Filter which looks a bit like: public function newQuery($excludeDeleted = true) { $query = parent::newQuery($excludeDeleted); if (!authUserIsAdmin()) { $query->where('company_id', Auth::user()->company_id); } return $query; } But now if we use an orWhere clause in our queries

nvangijzen's avatar
nvangijzen's avatar lostdreame...Laravel
1

Why the query shows "Property [certificate] does not exist on this collection instance."?

Do you know why this query is not working? To get the certificate content associated with the selected registration type. $certificate = RegistrationType::find($sendTo)->certificate; dd($certificate); Certificate model: class Certificate extends Model { protected $fillable = ['content']; public function registration_types(){ return

adamjhn's avatar
adamjhn's avatar tykusLaravel
5

Custom authentification query

How can I add DESC to the default login sql query? I mean on default is something like select * from users where name = user_name limit 1. How can I add select * from users where name = user_name ORDER BY id DESC limit 1?

grimdbu's avatar
grimdbu's avatar victordbuLaravel
4

[L5.*] Pagination: Change URL query name "page" globally

Hi, almost a year ago, I asked, if it's possible to globally change the name for the page query attribute if you enable pagination: Instead of having a url like tickets?page=23 I wanted to translate this to tickets?seite=23. Therefore, I created a lil' helper function to do so. I also use the following snippet inside my routes/web.php file to rename the routes from tickets/new

toby's avatar
toby's avatar tobyLaravel
2

How to query unicode character with Query Builder 'where' ?

How to query unicode character with Query Builder 'where' ? I have a JSON column that have unicode hex characters like '\u00e3' but I cant find them using Laravel's where methods. How can I solve that?

lucassimines's avatar
lucassimines's avatar lucassimin...Laravel
1

Raw query relationship trouble on view

In our controller we want to do the following query: $query = "SELECT * FROM posts WHERE "; the rest of the query will be filled with what the user filles out. In our view we are trying to display the names of users that are assigned to the post whitch looks like this. @php $allAssigned = ""; @endphp @if($ticket->assignedUsers->count() > 1)

ZHoving's avatar
ZHoving's avatar ZHovingEloquent
3

filter data in Query 2 tables

Hi I am having trouble in filtering 2 tables. I have Two table called "Flights" and "MultiFlightSegments". "Flight" contains information about flights and "MultiFlightSegments" contains informations of "Flights" table. Basically "Flight" hasMany retations to "MultiFlightSegments" . When I select "Flight

ncltours's avatar
ncltours's avatar lostdreame...Laravel
1

No query results for model [App\Shop\Categories\Category] 1

I just clone laravel app from github and now am facing different kinds of errors. After all the installation and setting I got this error: No query results for model [App\Shop\Categories\Category] 1. This is the my controller:

IsoftTech's avatar
IsoftTech's avatar bobbybouwm...Laravel
5

DB Transactions query looping alternative

Hi, Version: 5.4 Method: Query Builder after storing the .csv as an array $headers for columns, $data_rows for the data Aim: I want to add users phone numbers via a .csv file to my database. Problem: Is there a better method other than looping through each row? The entries can be up to 5000 users which results in 5000 insert queries. To ensure all are added at a go I have setup

ochirach's avatar
ochirach's avatar ochirachLaravel
3

Get Join query result in different JSON Object Key

I have a Query like below in my Laravel controller which one I am using for API development. return $post ->comments() ->join('translationCHN', function ($join) { $join->on('comment.word_id', '=', 'translationCHN.word_id'); $join->on('comment.sentence_id', '=', 'translationCHN.sentence_id');

afoysal's avatar
afoysal's avatar pinakmitha...Eloquent
3

contains() for Query Builder

I have some relationships between models (One to Many and Many to Many) and I need to check whether one entity belongs to another: if ($user->books->contains($book)) { // do something } This looks clean and readable and also it supports any kind of relationships. But there is a small problem with performance because we need to grab all user's books Collection. Query

chimit's avatar
chimit's avatar SnapeyEloquent
1

Sending URL Query String through Vue / Axios

I'm pretty new to Vue / Axios. What I'm trying to do is get and send the query string through to an email using Axios. The Query string is set from Google Adwords. The current PHP way is <input type="hidden" id="keyword" name="keyword" value="<?php echo $keyword; ?>"> http://website.co.uk/page.php?keyword=jh%20page&gclid=

TheGarden's avatar
TheGarden's avatar TheGardenVue
0

Better query with relations then this

Hi, I have db table with tournaments. Every tournaments has meny games. Every game has meny "plays" and every play belongs to user. My current query looks like this $tournament = $this->getTournament($slug); $gamesRounds = $tournament->games->groupBy('round'); which gives me collections of rounds which contains collections of games... and this is what I want

GreenGo's avatar
GreenGo's avatar GreenGoEloquent
3

Sql query to Eloquent.

Hi i want to make this sql query with laravel eloquent way any ideas. SELECT au.id au_id, ar.id ar_id, ar.created_at ar_created_at FROM authors au LEFT JOIN articles ar ON au.id=ar.author_id INNER JOIN (SELECT author_id, max(created_at) max_c from articles group by author_id) g ON g.author_id=au.id AND g.max_c=ar.created_at ORDER BY ar_created_at DESC

Mike_91's avatar
Mike_91's avatar staudenmei...Eloquent
1

Database query

i want to know how to apply where with AND condition in following query what am missing public function addfav(Request $request) { //echo $request->student_id; if (favourites::where('student_id', $request->student_id)->where('quiz_id', $request->quiz_id)->exists()) { echo 'studentid present';

jagdishabhiandroid's avatar
jagdishabhiandroid's avatar jagdishabh...Laravel
3

querybuilder pass column to nested query

I would like to query the db to retrieve all events that do not have a comment created after the start date. Following structure for EVENT and COMMENT EVENT id start created_at updated_at COMMENT id comment event_id created_at updated_at Create a query ( $query = Event::query()->with('comments'); ) which gives me the following construct. [{ "id": 1,

Lo5t's avatar
Lo5t's avatar CinekEloquent
1

Table name in output of Join() query

I have a Query like below. I am outputting json response. $users = DB::table('users') ->join('contacts', 'users.id', '=', 'contacts.user_id') // need table name as object key here ->select('users.*', 'contacts.phone', 'orders.price') ->get();

afoysal's avatar
afoysal's avatar afoysalEloquent
6

Very slow search/sort query

Hi, I have a search page that gives you articles sorted by the likes they get. But the page loads very slow 10 seconds! I know that the problem is with number of likes. In my view I show every post with number of likes they have. When I remove that and also remove The part where I include 'likes' the speed is OK. Is there anyway I can make it faster? Here is the search method i

AR's avatar
AR's avatar RobstarEloquent
4

Undefined variable $query

I want to do a query to get the registrations in conferences of the auth user that are like the text introduced by the user in the search form. But with code below it appears "Undefined variable $query". Do you know where is the error? public function searchRegistration(Request $request) { $pageLimit = 5; $user = Auth::user(); $search = $request->searc

adamjhn's avatar
adamjhn's avatar SnapeyLaravel
3

Laravel/SQL: How to fetch data from multiple table in a single query? that too using 'where'

Working on a search functionality on Laravel App(Blog/Posts). There are multiple types of posts (each having a separate table in the database) Like Business posts, Social Life posts etc.. Below is the search function on SearchController class SearchController extends Controller { public function search(Request $request, $query = null) { if($query == null) return red

ravipw1801's avatar
ravipw1801's avatar Tray2Laravel
6

Create And-OR - And Query with Elequent

How we can create sql query in laravel using Elequent select * from `products` where `status` = ? and `collection` = ? and (`product_category`= ? OR product_category=?) and (`designer`=? OR `designer`=?) order by `id` desc limit 15 offset 0

V9द's avatar
V9द's avatar _Artak_Eloquent
1

How do query scopes work exactly?

So I know there are differences from 5.6 and 5.4 but after looking through the documentation I can't figure out why this works. public function scopeFilter($query, $filter) { if ($month = $filter['month']) { $query->whereMonth('created_at', Carbon::parse($month)->month); } if ($year = $filter['year']) { $query->whereYear('created_at', $ye

bryantDinh's avatar
bryantDinh's avatar SnapeyLaravel
5

Using whereRaw condition on laravel eager loading with query builder

We want's to need those complain, which lifetime(created_at - now()) is grater then a complain lifetime(the lifetime amount stored on complain_type table) by eloquent relationship. ####01.complain table: |id | complain_preset_id | created_at | ####02. Complain Preset Table: |id | type_id | created_at | ####03. complain type table |id | lifetime | the relation be

pasha's avatar
pasha's avatar pashaLaravel
0

"is my pub currently open?". Problems using conditions in a query statement in Laravel

Hi there!! I'm trying the following: I have two models: Pubs and Schedules (where I store statically the pubs schedules). They 're related one to each other as follows: Pub: public function pubSchedules() { return $this->hasMany( Schedule::class ); } Schedule: public function pub() { return $this->belongsTo(Pub::class); } Table schedules has the following fi

Quadram's avatar
Quadram's avatar QuadramLaravel
0

Parameterize this Query

Still quite new to Laravel and I'm not sure how to parameterize this query. Essentially, you pass in latitude and longitude coordinates along with a city and it returns the cities that are close by. $cities = DB::select(DB::raw(' select concat(City, \', \',State) as displayText, City as city, State as state, mi

ncamm's avatar
ncamm's avatar narinder14...Eloquent
8