27 Matching Annotations
  1. Apr 2022
    1. LEFT OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.
    2. It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them.
    1. SELECT lateral_subquery.* FROM posts JOIN LATERAL ( SELECT comments.* FROM comments WHERE (comments.post_id = posts.id) LIMIT 3 ) lateral_subquery ON true WHERE posts.id
    2. You want the front page to show a few hundred posts along with the top three comments on each post. You’re planning on being very popular, so the front page will need to be very fast. How do you fetch that data efficiently from postgresql using Activerecord?
    3. Making one Comment query per Post is too expensive; it’s N+1 queries (one to fetch the posts, N to fetch the comments). You could use includes to preload all the comments for all the posts, but that requires hydrating hundreds of thousands of records, even though you only need a few hundred for your front page. What you want is some kind of GROUP BY with a LIMIT on each group — but that doesn’t exist, either in Activerecord nor even in postgres. Postgres has a different solution for this problem: the LATERAL JOIN.
    1. join = Arel::Nodes::NamedFunction.new('json_b_array_elements', [Arel::Nodes::SqlLiteral.new("subscriptions")]) .as(Arel::Nodes::NamedFunction.new('sd', [Arel::Nodes::SqlLiteral.new("subscription_data")]).to_sql) p = e.project( Arel::Nodes::SqlLiteral.new( Arel::Nodes::Grouping.new( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'id'"))).to_sql) << '::uuid' ).where( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'type'").eq( Arel::Nodes::SqlLiteral.new("'Company'") ) ).and(e[:slug].eq(event_slug))) p.join_sources << Arel::Nodes::StringJoin.new( Arel::Nodes::SqlLiteral.new('CROSS JOIN LATERAL')) << join
  2. Mar 2022
    1. And that can all can be written with CROSS JOIN LATERAL which is much cleaner, SELECT ARRAY( SELECT DISTINCT e FROM ( VALUES ('foo', 'bar', 'foo', 'baz' ) ) AS t(a,b,c,d) CROSS JOIN LATERAL unnest(ARRAY[a,b,c,d]) AS a(e) -- ORDER BY e; -- if you want it sorted );
  3. Jun 2021
  4. www.postgresql.org www.postgresql.org
    1. A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.
    1. SELECT base.nr, multiples.multiple FROM (SELECT generate_series(1,10) AS nr) base, LATERAL ( SELECT multiples.multiple FROM ( SELECT generate_series(1,10) AS b_nr, base.nr * 2 AS multiple ) multiples WHERE multiples.b_nr = base.nr ) multiples;
    1. SELECT s.id, s1.percent_water , s1.percent_water * 100 AS percent_water_100 FROM samples s , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;
    1. The age_in_years is calculated for every record of the blog table. So, it works like a correlated subquery, but the subquery records are joined with the primary table and, for this reason, we can reference the columns produced by the subquery.
    1. SELECT * FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID)
    2. In a FROM clause, the LATERAL keyword allows an inline view to reference columns from a table expression that precedes that inline view.
    1. Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) TL;DR - LATERAL allows subqueries to reference earlier tables.
  5. Aug 2020
  6. Jun 2018
    1. The preservation of meets and joins, and hence whether a monotone map sustainsgenerative effects, is tightly related to the concept of a Galois connection, or moregenerally an adjunction.
    2. In his work on generative effects, Adam restricts his attention to maps that preservemeets, even while they do not preserve joins. The preservation of meets implies that themapbehaves well when restricting to a subsystem, even if it can throw up surpriseswhen joining systems
    3. n [Ada17], Adam thinks of monotone maps as observations. A monotone map:P!Qis a phenomenon ofPas observed byQ. He defines generative effects of such a mapto be its failure to preserve joins (or more generally, for categories, its failure topreserve colimits)
    4. Example1.61.Consider the two-element setPfp;q;rgwith the discrete ordering.The setAfp;qgdoes not have a join inPbecause ifxwas a join, we would needpxandqx, and there is no such elementx.Example1.62.In any posetP, we havep_pp^pp.Example1.63.In a power set, the meet of a collection of subsets is their intersection,while the join is their union. This justifies the terminology.Example1.64.In a total order, the meet of a set is its infimum, while the join of a set isits supremum.Exercise1.65.Recall the division ordering onNfrom Example 1.29: we say thatnmifndivides perfectly intom. What is the meet of two numbers in this poset? Whatabout the join?

      These are all great examples. I htink 1.65 is gcd and lcm.

  7. Oct 2016