today-i-learned


We have three tables:

- Folders (`id`, `company_id`)
- Milestones (`id`, `is_done`, `value`: i.e. when the milestone will happen)
- Members (`folder_id`, `user_id`): a member of this folder

A folder has multiple milestones and multiple members. We would like to get a
list of `(folder.id, max(milestone.value), member.user_id)` for each folder ID
and member user ID.

As usual with SQLAlchemy, it is a good idea to start by writing the query in
plain SQL. Here's one way to do it:

```sql
select f1.*, m.user_id from (
    select f.id, max(m.value)
    from folders f
    join milestones m on m.folder_id = f.id
    where f.company_id = :id group by f.id)
as f1

left join folders_members m on m.folder_id = f1.id
order by f1.id
```

And here's how to write it with SQLAlchemy core:

```python
f = t_folders.c
m = t_milestones.c
members_t = t_folders_members

f1 = (
    select([f.id, func.max(m.value).label("value")])
    .select_from(t_folders.join(t_milestones, f.id == m.folder_id))
    .where(
        and_(
            f.company_id == company_id,
            m.is_done.is_(False),
        )
    )
    .group_by(f.id)
).alias("f1")
f2 = select([f1.c.id, f1.c.value, members_t.c.id]).select_from(
    f1.join(members_t, members_t.c.folder_id == f1.c.id)
)

res = self.session.execute(f2)
```


Writing a subquery with SQLAlchemy core


Pretty straightforward:

```vim
%!jq '.'
```


Pretty print JSON inside Vim using jq


We have two tables:

- Folders (`id`, `company_id`)
- Milestones (`id`, `is_done`, `value`: i.e. when the milestone will happen)

A folder has multiple milestones. We would like to get each folder as well
as its most recent milestone.

As usual with SQLAlchemy, it is a good idea to start by writing the query in
plain SQL. Here's one way to do it (see [4 Ways to Join Only The First Row in SQL](https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql)):

```sql
select *
from folders f

join milestones m on m.id = (
select id from milestones m
where m.folder_id = f.id and f.company_id = ... and m.is_done = false
order by `value` asc
limit 1
)
```

And here's how to write it with SQLAlchemy:

```python
subq = (session.query(TMilestone.id).filter(
    TMilestone.is_done.is_(False),
    TFolder.company_id == company_id).order_by(
        TMilestone.value.asc()).limit(1).correlate(TFolder))

query = session.query(TFolder, TMilestone).join(
    TMilestone, TMilestone.id == subq)

res = query.all()
```


Writing a correlated subquery with SQLAlchemy


Here's a very crude way to run `pytest` only on files that were added or
modified:

```bash
pytest $(git ls-files --other --modified --exclude-standard | grep test)
```


Running pytest on modified files


This turns out to be pretty simple. Here's how it looks like before, with
`justify-content: space-between`:

![Screenshot showing space-between](/img/flexbox-space-between.png)

The space is spread inside the `transaction` div. We'd like the first two
columns (the icon and the text) to be aligned left, and the amount to be all
the way to the right.

We just need to make the last div (in this case, `.amount`) like this:

```css
.amount {
  margin-left: auto;
}
```

![Screenshot showing flex with margin auto](/img/flexbox-margin-auto.png)

That's it!

_Note: this example is taken from the excellent [FrontLoops](https://frontloops.io/) exercise_


Aligning the last flexbox item to the right with margin auto

How to align the last flexbox item to the right.


Here's how to generate LaTeX URL in a document:

```python
import re

RE_URL = re.compile(
    r"(https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,256}\.[a-z]{2,6}\b([-a-zA-Z0-9@:%_\+.~#?&//=]*))"
)
text = RE_URL.sub(r"\url{\1}", text)
```


How to latexify URLs in a doc with a Regex

Category: today-i-learned

Writing a subquery with SQLAlchemy core

Pretty print JSON inside Vim using jq

Writing a correlated subquery with SQLAlchemy

Running pytest on modified files

How to align the last flexbox item to the right.

Latexify a URL with a Regex