I have a table posts which has a many-to-many relationship with tags, the pivot table is called posts-tags. I want to be able to retrieve all posts by a list of tag id’s. Imagine With tag id’s [1, 2, 3], I should get back [{id: 1, text: “foo”}] With tag id’s [1], I should get back [{id: 1, text: “foo”},
Tag: sql
Get distinct members for an array of group IDs
I have 3 tables: Scorecard Group Scorecard_Group (Joint table) I am trying to write a query that fulfills the following purpose: Get the list of distinct scorecard ids WHERE Scorecard_Group.groupId IN (Pass array of groupIds) Get all Scorecard.”name” where Scorecard.”id” IN (Array of scorecardIds that we just queries) I am able to achieve this using 2 queries but I would
Parse utm parameters in sql query
I have a table in my database that tracks visitors page views and I am trying to pull out the utm tracking parameters from the url You can use this script to create table This my script I am getting funny results where if there is no utm tracking it is still populating fields and the utm_id is incorrect in
SQL Query for Latest Snapshot From Bitemporal Data
Given a table of data with bitemporal modeling, where there are 2 dates: (i) the date that the data applies to, and (ii) the date at which the fact was known. What SQL query will give the latest snapshot of the data date based on the As_of_Datetime? e.g. Answer
SQL: date type column with only mm-dd
I want to create a column of data type having only ‘mm-dd’ values. Is it possible and if yes how should I do it? Note: Instead of “2022-06-07”, I want “07-06” Answer There is no date type that can store that format – in fact none of the date types store a date and/or time in any of the formats
How to extract value from middle of substring in SQL?
I have column called “CustomerId” with value “1_Nissan_028” and “2_Ferrari_035”. I would like to extract “Nissan” or “Ferrari” as “CustomerName”. CustomerName is located in middle of CustomerId and lenght varies. Following SQL query return values like “Nissan_” or “Ferrar”. How to write SQL statement? Answer Assuming that the value is always the 2nd delimited value, you can use STRING_SPLIT and
PostgreSQL – Adding up json values
This is my json column in pgSQL and I need to add up all of the “sum” amounts using pgSQL. So the query should return “50” using the data below. I did find something like this (Get aggregate sum of json array in Postgres NOSQL json data) that kinda works if I had my values in array, but I dont,
How to use a SQL Result in Discord.JS SelectMenus?
I am trying to add Options to a SelectMenu from a SQL request. My code is the following: My Console is giving me the following output: There are no Errors. Still Discord doesn’t add it. It looks like this: Discord View of the Select Menu I have no clue, what i did wrong. Please help me out. Thanks in advance
How to optimize my query speed (avoid using subselect for every row)?
I have a table called CisLinkLoadedData. Is has Distributor, Network, Product, DocumentDate, Weight, AmountCP and Quantity columns. It used to store some product daily sales. AmountCP / Quantity is the price for the product at certain date. There are promo and regular sales, but no flag for it. We can tell if certain record is regular or promo by comparing
Implement Scan and Value functions in golang
I am trying to store some golang objects in SQL database and have implemented the scanner and value interface as follows: Is there a way that I can pass the parameter to the Value() function by pointers, as I am getting a HugeParam error that the attr passed to the Value() function is too big, when trying to convert the