PaulieScanlon

WITH happy_reactions AS (
  SELECT analytics.city, analytics.country, analytics.flag, COUNT(reactions.id) AS count
  FROM analytics
  INNER JOIN reactions ON analytics.slug = reactions.slug
  WHERE reactions.reaction = 'happy' AND analytics.date >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY analytics.city, analytics.country, analytics.flag
)
SELECT city, country, flag, count
FROM happy_reactions
ORDER BY count DESC

PaulieScanlon

.select({
  id: orders.order_id,
  order_date: orders.order_date,
  first_name: orders.first_name,
  last_name: orders.last_name,
})
.from(orders)
.innerJoin(customers, eq(orders.order_id, customers.customer_id))
.limit(50);

PaulieScanlon

SELECT 
  orders.order_id,
  orders.order_date,
  orders.first_name,
  orders.last_name
FROM orders
INNER JOIN customers ON orders.order_id = customers.customer_id
LIMIT 50;

PaulieScanlon

.select({
  id: users.user_id,
  first_name: users.first_name,
  last_name: users.last_name,
  email: users.email,
  role: users.role,
})
.from(users)
.where(eq(users.role, 'admin'))
.orderBy(users.last_name);

PaulieScanlon

SELECT 
  user_id,
  first_name,
  last_name,
  email,
  role
FROM users
WHERE role = 'admin'
ORDER BY last_name;

PaulieScanlon

import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  user_id: serial('user_id').primaryKey(),
  first_name: varchar('first_name', { length: 100 }),
  last_name: varchar('last_name', { length: 100 }),
  email: varchar('email', { length: 80 }).unique(),
  role: varchar('role', { length: 20 }),
});

PaulieScanlon

CREATE TABLE USERS (
  user_id     SERIAL PRIMARY KEY, 
  first_name  VARCHAR(100), 
  last_name   VARCHAR(100), 
  email       VARCHAR(80) UNIQUE, 
  role        VARCHAR(20)
);

PaulieScanlon

.select({
  city: analytics.city,
  country: analytics.country,
  flag: analytics.flag,
  count: sql`COUNT(${reactions.id})`.as('count'),
})
.from(analytics)
.innerJoin(reactions, eq(analytics.slug, reactions.slug))
.where(and(eq(reactions.reaction, 'happy'), gte(analytics.date, new Date(new Date().getTime() - 30 * 24 * 60 * 60 * 1000))))
.groupBy(analytics.city, analytics.country, analytics.flag)

PaulieScanlon

xata.db['users'].select(['name', 'country', 'email']).getMany();

PaulieScanlon

supabase.from('users').select('name, country, email');