Filter wordpress query with SQL
I want to create a custom filter for the wordpress posts query. I tested the SQL query, and it returns the posts. When added to the parse_query hook it generates an SQL sytax error. I think it's too complex to…
Postgresql – Casting to a nested type in Postgres
Is it possible to cast a value to an Array or a Struct with the normal cast syntax? For example, similar to how I can do: SELECT '1'::INT, CAST('1' AS INT) CAST('1' AS INT) Am I able to do something…
Postgresql – Aggregate multiple detail rows in a join query
I have a couple of tables where I'm applying inner join to fetch matching rows: event (id, url, name, eventStart, eventEnd)` slot (id, event_id date, start_time, end_time, location_type, location_id)` Query: select * from event as evnt inner join slot as…
Postgresql – Get specific date, from specific day last week
I'm planning to get date from specific day from last week in node.js For example if today is Friday or any day, I want to know what date is Sunday from the last week I'm thinking to have this solution…
Postgresql – Find rows with fewer than X associations (including 0)
I have students associated to schools, and want to find all schools that have five or fewer (including zero) students that have has_mohawk = false. Here's an Activerecord query: School.joins(:students) .group(:id) .having("count(students.id) < 5") .where(students: {has_mohawk: true}) This works for…
PostgreSQL generate and increment alphanumeric series
as the question says, I need postgresql procedure which will generate Some ids like: AX0 AX1 AX2 . . AX9 AXA AXB . . AXZ AY0 AY1 . . AY9 AYA AYB . . AZZ B00 B01 B02 . .…
Postgresql – How to make a UNION (or JOIN) query between two tables where 1st has higher priority without duplicates?
I am trying to make a performant UNION (or JOIN) query between two tables: tableA +----+ | id | +----+ | 1 | | 2 | | 7 | +----+ tableB +----+ | id | +----+ | 1 | |…
Postgresql – SQL getting rows with a flag value without using functions
I have a following table I need to only select all the values with sensorfield1 flag = r and reject the rows with corresponding IDs with flag = h and select those values for IDs where flag value is only…
Postgresql – Search if jsonb array of string contains value
I have arry of strings in a column like : ["196 2616", "9503744", "36.25260-6027", "2 414 425", "7 034 771 6", "F709714", "1088229", "183144", "505870338", "105075"] I want to search if this array contains for example 2 414 425. I…
Postgresql – Uniting two statements in SQL (using JOIN or more efficient way)
I made two statements - SUM of revenue and expenses in different tables using group by id (each id has more than one revenue and expenses records): select id_person, SUM (revenue_d) as sum_revenue from sales group by id_person; select id_person,…