Jordash
Jordash

Reputation: 3093

Perform search based on seperate table data in PostgreSQL

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

Answers (2)

Akli REGUIG
Akli REGUIG

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

Julio Ojeda
Julio Ojeda

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

Related Questions