Reputation: 3093
I have a table which looks like this:
deals
id vendorid name
1 52 '25% Off'
2 34 '10% Off'
-
vendors
id name
52 'Walmart'
34 'Home Depot'
I'm trying to do a search of the database that also searches based on the vendor id.
I was able to do this to get the vendor data in the query:
SELECT *,
(SELECT json_agg(vendors.*) FROM vendors WHERE deals.vendorid = vendors.id) as vendor
FROM deals
Now I want to add a search to it, here is something I tried
SELECT *,
(SELECT json_agg(vendors.*) FROM vendors WHERE deals.vendorid = vendors.id) as vendor
FROM deals
WHERE vendor.name ILIKE '%wal%'
In the above example the user would be starting a search for walmart
however it says that the vendor.name column does not exist, what would the correct way to do this be?
The output I'm expecting from the above query is:
[
{
id: 1,
vendorid: 52,
name: '25% Off',
vendor: [
{
id: 52,
name: 'Walmart'
}
]
}
]
Upvotes: 1
Views: 35
Reputation: 562
The query you need
drop table if exists deals;
drop table if exists vendors;
create table if not exists deals(
id serial,
vendorid int not null,
name varchar not null
);
create table if not exists vendors(
id serial,
name varchar not null
);
insert into deals values
(1,52,'25% off'),
(2,34,'10% off');
insert into vendors values
(52,'Walmart'),
(34,'Home Depot');
select
d.id,
d.vendorid,
v.name,
json_agg(v.*)
from
vendors v
left join
deals d on d.vendorid = v.id
where
--v.id=52 and
v.name ILIKE '%wal%'
group by
d.id,
d.vendorid,
v.name;
If you are running a 9.3+ version of postgres, you can use a great feature provided by postgres : the full text search
check how it works here
Enjoy postgres power.
Upvotes: 1
Reputation: 817
I was able to get it done by using this.
SELECT
*,
(SELECT json_agg(vendors.*) FROM vendors WHERE deals.vendorid = vendors.id) as vendor
FROM
deals
where (SELECT json_agg(vendors.*) FROM vendors WHERE deals.vendorid = vendors.id).name ILIKE '%wal%'
It's pretty ugly but get the work done. I am not really familiar with postgree but you can store it in a variable somehow.
Upvotes: 2