I have a model defined as follows: I’m trying to use Sequelize to load all game object and include the User with the id equal to the playerId field. The problem is I have two attributes (leaderId, playerId) which reference the User model so using include as follows does not work: Is there a way to speci…
Tag: sql
Convert utc string to date SQL
I’ve a column sample_date in form of string as 200912301111230000000000 (UTC Time).How can I convert it from string to datetime in form of yyyymmdd using SQL select statement? Answer As you only want yyyymmdd, which is the first 8 chacters of your string, it is enough to simply use LEFT I added the ST_T…
Dynamic TSQL Pivot without aggregate function
I have a table like this (‘ExternalPersonRelationTable’) PersonId SubjectCode 4187 3 4187 278 4429 3 4429 4 4463 99 4464 174 4464 175 I want to rotate the data so that every person in the table gets a column and a TRUE/FALSE value for each subject code, i.e. a table like this: Code 4187 4429 4463 …
Spark SQL : filtering a table on records which appear in another table (two columns)?
I have several tables, and I would like to filter the rows in one of them, based on whether two columns are present in another table. The data on each table is as follows Table1 : one hash can be associated to several articles ; one article can be associated to several hashes User Hash Article Name Hash1 Arti…
Page numbers & total result count in a cursor based pagination
Though it may sound like a stupid question, sometimes it is necessary to show page numbers (and also the last page). What is the best way to calculate total row counts and also calculate page numbers (by providing a page query to return specific page) in a cursor based pagination? Also are all these possible …
SQL union grouped by rows
Assume I have a table like this: col1 col2 col3 col4 commonrow one two null commonrow null null three How to produce a result to look like this: col1 col2 col3 col4 commonrow one two three Thanks Answer like this, you can group by col1 and get the maximum in each group:
How to perform translation from RAW SQL to django queryset
I am struggling with conversion to django query having raw sql I am new in django and any help will be appreciated There are simple models: Winemaker – target model Wine Post Winemaker has 1+ Wines Wine has 1+ Posts I know that it should be done with annotations but have no idea how to implement it. Ans…
Keep multiple rows during PIVOT in Snowflake
I would like to transpose rows into columns in Snowflake. Suppose I have the following table BASE ID value type 1 100 ‘A’ 1 200 ‘B’ 1 300 ‘B’ 2 400 ‘A’ The output should be as follows: ID A B 1 100 200 1 100 300 2 400 NULL Currently I am pivoting the table with …
Is there a best way to join multiple tables
Can some one please help in joining/merging the table like below. I know how to do if the department columns (depart_1, depart_2, depart_3) are in one table. but not able to achieve this scenario as they are in different tables. I have almost 100 fields like department, so little concern about performance as …
How to Fetch specific number of characters from a string using gorm?
I am using SQLite, if I have a text post or an article that is a 400 character string for example, I want to extract only the first 100 character from it to be used in the front-end. In line 4 i extracted the latest 5 posts from the db, but i want to limit the body of the post