dogsheep / github-to-sqlite

Save data from GitHub to a SQLite database
https://github-to-sqlite.dogsheep.net/
Apache License 2.0
405 stars 43 forks source link

Add this repos_starred view #10

Closed simonw closed 4 years ago

simonw commented 4 years ago
create view repos_starred as select
  stars.starred_at,
  users.login,
  repos.*
from
  repos
  join stars on repos.id = stars.repo
  join users on repos.owner = users.id
order by
  starred_at desc;
simonw commented 4 years ago

This definition isn't quite right - it's not pulling the identity of the user who starred the repo (users.login ends up being the owner login instead).

simonw commented 4 years ago

Blocked on #37

simonw commented 4 years ago

Fixed definition:

select
  stars.starred_at,
  starring_user.login as starred_by,
  repos.*
from
  repos
  join stars on repos.id = stars.repo
  join users as starring_user on stars.user = starring_user.id
  join users on repos.owner = users.id
order by
  starred_at desc;