OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Supabase (PostgreSQL) brain teaser

  • Thread starter Thread starter pawel_s
  • Start date Start date
P

pawel_s

Guest
I have a 3 tables:

  • services - info about services (id, title)
  • city - info about city (id, city_name)
  • service_city - map multiple cities to services (service_id, city_id)

and I need functionality to select only those services which contain certain city provided by user. So far I have got is solution below:

Code:
const cityToSearch = {
    id: 1,
    city_name: 'Amsterdam'
}

Code:
let query = supabase
    .from('service')
    .select('title, city(id,city_name)'
    )

if (search) query.ilike('title', `%${search}%`);
if (city) {
    query.eq('city.id', cityToSearch.id);
    query.not('city', 'is', null);
}

query.range(from, to);
const { data, error } = await query;

The problem is it returns me array of cities with only one city that I search for:

Code:
[
  {
    title: 'Service 1',
    id: 'service_1',
    city: [ 
      {
        id: 1,
        city_name: 'Amsterdam'
      } 
    ],
  },
  {
    title: 'Service 2',
    id: 'service_2',
    city: [ 
      {
        id: 1,
        city_name: 'Amsterdam'
      } 
    ],
  }
]

however, I need an array of cities that include my cityToSearch (ex.):

Code:
[
  {
    title: 'Service 1',
    id: 'service_1',
    city: [ 
      {
        id: 1,
        city_name: 'Amsterdam'
      },
      {
        id: 2,
        city_name: 'Paris'
      }
    ],
  },
  {
    title: 'Service 2',
    id: 'service_2',
    city: [ 
      {
        id: 1,
        city_name: 'Amsterdam'
      },
      {
        id: 3,
        city_name: 'Krakow'
      }
    ],
  }
]

Supabase documentation: https://supabase.com/docs/reference/javascript

Any ideas? : )

<p>I have a 3 tables:</p>
<ul>
<li><strong>services</strong> - info about services (id, title)</li>
<li><strong>city</strong> - info about city (id, city_name)</li>
<li><strong>service_city</strong> - map multiple cities to services (service_id, city_id)</li>
</ul>
<p>and I need functionality to select only those services which contain certain city provided by user.
So far I have got is solution below:</p>
<pre class="lang-js prettyprint-override"><code>const cityToSearch = {
id: 1,
city_name: 'Amsterdam'
}
</code></pre>
<pre class="lang-js prettyprint-override"><code>let query = supabase
.from('service')
.select('title, city(id,city_name)'
)

if (search) query.ilike('title', `%${search}%`);
if (city) {
query.eq('city.id', cityToSearch.id);
query.not('city', 'is', null);
}

query.range(from, to);
const { data, error } = await query;
</code></pre>
<p>The problem is it returns me array of cities with <strong>only one city</strong> that I search for:</p>
<pre class="lang-js prettyprint-override"><code>[
{
title: 'Service 1',
id: 'service_1',
city: [
{
id: 1,
city_name: 'Amsterdam'
}
],
},
{
title: 'Service 2',
id: 'service_2',
city: [
{
id: 1,
city_name: 'Amsterdam'
}
],
}
]
</code></pre>
<p>however, I need an array of cities that <strong>include</strong> my cityToSearch (ex.):</p>
<pre class="lang-js prettyprint-override"><code>[
{
title: 'Service 1',
id: 'service_1',
city: [
{
id: 1,
city_name: 'Amsterdam'
},
{
id: 2,
city_name: 'Paris'
}
],
},
{
title: 'Service 2',
id: 'service_2',
city: [
{
id: 1,
city_name: 'Amsterdam'
},
{
id: 3,
city_name: 'Krakow'
}
],
}
]
</code></pre>
<p>Supabase documentation: <a href="https://supabase.com/docs/reference/javascript" rel="nofollow noreferrer">https://supabase.com/docs/reference/javascript</a></p>
<p>Any ideas? : )</p>
 

Latest posts

Top