LEFT JOIN in Postgres
Ok what's wrong with this SQL query:
select j.* from judge as j left join user as u on j.user_id = u.id where u.email="firstname.lastname@example.org"; ERROR: column "email@example.com" does not exist
You are using double quotes where you should actually be using single quotes. Single quotes denote values, while double quotes represent columns and tables, etc. You can find out more about the difference here.
See also questions close to this topic
Creating multiple databases with doctrine based on multiple connections
I am facing an issue when creating multiple databases based on multiple connections for functional testing purposes.
I created on a Symfony 3.3 application due to some restrictions and an update to a maintained version of Symfony will be scheduled. The thing to keep in mind is that I had to deal with an existing PostgreSQL database, especially with 3 databases:
- database A
- database B
- database C
I dislike designing my application from an existing database. Usually when I create an application from scratch, I design my application and all business rules first but I had no choice here. Sounds bad but this part of my application is designed from the existing database. Any suggestion is most welcome anyway. So, in App\Entity folder I configured the mapping for the 3 connections:
- entities related to database A are in App\Entity\Dba folder
- entities related to database B are in App\Entity\Dbb folder
- entities related to database C are in App\Entity\Dbc folder
Using flex, the doctrine configuration in the doctrine.yaml file looks like this:
doctrine: dbal: default_connection: a connections: a: driver: '%database_a_driver%' url: '%env(DATABASE_A_URL)%' charset: UTF8 server_version: '%server_version%' b: driver: '%database_b_driver%' url: '%env(DATABASE_B_URL)%' charset: UTF8 server_version: '%server_version%' c: driver: '%database_c_driver%' url: '%env(DATABASE_C_URL)%' charset: UTF8 server_version: '%server_version%' orm: auto_generate_proxy_classes: '%kernel.debug%' default_entity_manager: em_a entity_managers: em_a: connection: a mappings: AppDba: is_bundle: false type: annotation dir: '%kernel.project_dir%/src/Entity/Dba' prefix: 'App\Entity' alias: AppDba em_b: connection: b AppDbb: is_bundle: false type: annotation dir: '%kernel.project_dir%/src/Entity/Dbb' prefix: 'App\Entity' alias: AppDbb em_c: connection: c AppDbc: is_bundle: false type: annotation dir: '%kernel.project_dir%/src/Entity/Dbc' prefix: 'App\Entity' alias: AppDbc
Now comes the issue.
When I run this command:
bin/console doctrine:database:create --connection=a
The corresponding database is created with the correct name and the default public schema which is empty is also created. It is normal because I did not execute the
doctrine:schema:create --em=em_acommand yet.
But there is also a schema with some tables inside which are also created. And this schema does not belong to the a database but it belongs to the b database. And the strange thing is that those tables inside this unexpected schema are not configured in any mapping in my application. They are only in the existing database.
Does anyone know the origin of this issue and how to solve it?
- Symfony 3.3.18
- PostgreSQL 9.4.17
- doctrine orm 2.5.9-stable
- doctrine dbal v2.6.3
- doctrine bundle 1.9.1
Distinct does not work with select clause
I have 2 tables
class DepartmentBudget < ApplicationRecord has_many :budget_details, dependent: :destroy end class BudgetDetail < ApplicationRecord belongs_to :department_budget end
Each department have budgets for specific period of time. and each department have many budget_details which consist of all credit and debit entries and maintains remaining balance.
Issue is I want to apply sorting on department budget based on last record of budget detail's balance attribute.
DepartmentBudget .joins(:budget_details) .select('budget_details.balance, department_budgets.*') .order("budget_details.balance ASC") .distinct
I have implemented above query but it's returning duplicate records. the issue is with select clause if I remove select then distinct works, but I have to use select with distinct.Any help will be appreciated.
Postgis ST_DWithin function in laravel(lumen) query
I found an issue with postgis functions inside laravel(lumen) query builder.
Lumen version: 5.6
Postgres: 9.6.9 with postGIS
I have a code that works:
$sql = "ST_DWithin(location ,'POINT($lat $lon)', $distance)"; $query->whereRaw($sql);
This works but I'd like to pass parameters via parameter binding:
$sql = "ST_DWithin(location ,'POINT(? ?)', ?)"; $query->whereRaw($sql, [$lat, $lon, $distance]);
At first glance looks good, but it it returns an error:
Invalid parameter number: parameter was not defined (SQL: select * from "my_table" where ST_DWithin(location ,'POINT(123 123)', 1000)
I tried other combinations and that works:
$point = 'POINT($lat $lon)'; $sql = "ST_DWithin(location ,?, ?)"; $query->whereRaw($sql, [$point, $distance]);
so the problem seems to be with the
Can I join on a CONCAT with fields from both tables?
I'm currently trying to join two tables using a unique identifier.
SELECT CONCAT(OTable.c_s,'-',CTable.p_p_c,'-',CTable.d_p_c) OTable.carrier_delivery_sla, CTable.transit_days FROM *TABLE* AS OTable LEFT JOIN *TABLE* AS CTable ON OTable.CONCAT(OTable.c_s,'-',CTable.p_p_c,'-',CTable.d_p_c) = CTable.CONCAT(OTable.c_s,'-',CTable.p_p_c,'-',CTable.d_p_c)
Any suggestions on how to make this work?
select single row of multiple records in One to Many relation tables
In MSSQL, I have two tables with one to many relation.
tb_Products: ID ProductName tb_Images: ProductID MidPath
In Stored Procedure, shows products with their images (also shows null records), And if there is multiple productIDs in tb_Images, it shows a single record.
I can make this work by this Query:
declare @rowIndexToFetch int set @rowIndexToFetch=0 select p.ID,p.ProductName,p.CategoryID,c.ParentId, pi.MidPath from tb_Products p join tb_Categories c on p.CategoryID=c.ID left outer join tb_Images pi on pi.ProductID=p.ID where c.ID=3 or c.ParentId=3 order by pi.ID desc offset @rowIndexToFetch rows fetch next 1 rows only
But, if I use offset and fetch, I no longer can retrieve NULL records from tb_Images. Left outer join does not work anymore.
Example record returns without offset-fetch:
ID ProductName CategoryID ParentId MidPath 154 Chef Ceket 33 3 /cdn/www.sample.com/x 154 Chef Ceket 33 3 /cdn/www.sample.com/y 154 Chef Ceket 33 3 /cdn/www.sample.com/z 1 Eldiven 3 3 NULL
ID ProductName CategoryID ParentId MidPath 154 Chef Ceket 33 3 /cdn/www.sample.com/x
expected to return:
ID ProductName CategoryID ParentId MidPath 154 Chef Ceket 33 3 /cdn/www.sample.com/x 1 Eldiven 3 3 NULL
The problem is, I cannot retrieve null records when I use offset-fetch statement. How can I fix this?
How i write this Query. Want not use Eloquent
Please can anyone tell me the query? I use Laravel 5.7 with mysql.
-A User can have many recipes(one-to-many)
-A recipe can have many flavours and a flavour can be in many recipes.(many-to-many)
How i can get all recipes(with his flavours) from a given user? I want use the Laravel Querybuilder, not Eloquent.
I was trying this, but its not working:
$allRecipesFromUserX = DB::table('flavour_recipe') ->join('flavours', 'flavours.id', '=', 'flavour_recipe.flavour_id') ->join('recipes', 'recipes.id', '=', 'flavour_recipe.recipe_id') ->join('users', 'users.id', '=', 'recipes.id') ->get();