select count(*) filter (where year <= 1945) as pre1945, count(*) filter (where year between 1946 and 1964) as period2, count(*) filter (where year between 1965 and 1974) as period3, ... from ... where ...;

Die onderste link wordt veroorzaakt door deze query (dank Joost Plattel ):```oqlname: "This day in my history"query: {$and: [{"path": "'Deze dag op"}, {"title": "'11-04"}]}template: 'list'fields: ['title']sort: 'title'badge: false```Deze query verwijst naar een uniek .md bestand met als titel de maand en de dag van vandaag.Zo heb ik voor de 365 en soms 366 dagen per jaar een uniek bestandje.Door op de link te klikken kom ik op de pagina van vandaag in mijn persoonlijke geschiedenis:

TL;DR For classic Rails apps we have a built-in scope for preloading attachments (e.g. User.with_attached_avatar) or can generate the scope ourselves knowing the way Active Storage names internal associations.GraphQL makes preloading data a little bit trickier—we don’t know beforehand which data is needed by the client and cannot just add with_attached_<smth> to every Active Record collection (‘cause that would add an additional overhead when we don’t need this data).That’s why classic preloading approaches (includes, eager_load, etc.) are not very helpful for building GraphQL APIs. Instead, most of the applications use the batch loading technique.

We assume the AI will generate what a human collaborator might generate given the prompt.

we can improve AI interfaces by enabling conversational interactions that can let users establish common ground/shared semantics with the AI, and that provide repair mechanisms when such shared semantics are missing.

Collaborating with another human is better than working with generative AI in part because conversation allows us to establish common ground, build shared semantics and engage in repair strategies when something is ambiguous.

finding effective prompts is so difficult that there are websites and forums dedicated to collecting and sharing prompts (e.g. PromptHero, Arthub.ai, Reddit/StableDiffusion). There are also marketplaces for buying and selling prompts (e.g. PromptBase). And there is a cottage industry of research papers on prompt engineering.

Writing json_populate_record in the FROM clause is good practice, since all of the extracted columns are available for use without duplicate function calls.

Post.in_order_of(:type, %w[Draft Published Archived]).order(:created_at).pluck(:name) which generates SELECT posts.name FROM posts ORDER BY CASE posts.type WHEN 'Draft' THEN 1 WHEN 'Published' THEN 2 WHEN 'Archived' THEN 3 ELSE 4 END ASC, posts.created_at ASC

Then in your daily note Template add a tag that flags it as a daily note somewhere (eg. “#journal”. Also add an embedded query that looks like this: ```query {{date}} -"#journal" ```

As an example you might create 5 issues in between counts, which would cause the query count to increase by 5 if an N+1 problem exists.

QueryRecorder is a tool for detecting the N+1 queries problem from tests.

SELECT * FROM ( -- build virtual table of all hours between -- a date range SELECT start_ts, start_ts + interval '1 hour' AS end_ts FROM generate_series( '2017-03-01'::date, '2017-03-03'::timestamp - interval '1 hour', interval '1 hour' ) AS t(start_ts) ) AS cal LEFT JOIN ( -- build virtual table of uptimes SELECT * FROM ( VALUES ('2017-03-01 01:15:00-06'::timestamp, '2017-03-01 02:15:00-06'::timestamp), ('2017-03-01 08:00:00-06', '2017-03-01 20:00:00-06'), ('2017-03-02 19:00:00-06', null) ) AS t(start_ts, end_ts) ) AS uptime ON cal.end_ts > uptime.start_ts AND cal.start_ts <= coalesce(uptime.end_ts, current_timestamp)

FROM test x1 LEFT JOIN test x2 ON x1.id = (x2.data->>'parent')::INT;

where_values_hash.reduce(&:or)

scope :deck_access, ->(user) { includes(:deck_permissions).where(where({ deck_permissions: { user_id: user.id, read_access: true } }, global_deck_read: true, user_id: user.id).where_values_hash.reduce(&:or)) }

If those comments are loaded outside of the blog_post association, then attempting to reference the blog_post association from within each comment will result in N blog_posts table queries even if they all belong to the same BlogPost!

Joins are not expensive. Who said it to you? As basically the whole concept of relational databases revolve around joins (from a practical point of view), these product are very good at joining. The normal way of thinking is starting with properly normalized structures and going into fancy denormalizations and similar stuff when the performance really needs it on the reading side. JSON(B) and hstore (and EAV) are good for data with unknown structure.

The SELECT part should not contain any columns not referenced in GROUP BY clause, unless it is wrapped with an aggregate function.

Before SQL3 (1999), the selected fields must appear in the GROUP BY clause

Event.joins(:packages).having('array_agg(packages.type) @> array[?]', packages).group(:id)

Which to use? ANY is a later, more versatile addition, it can be combined with any binary operator returning a boolean value. IN burns down to a special case of ANY. In fact, its second form is rewritten internally: IN is rewritten with = ANY NOT IN is rewritten with <> ALL