foundersandcoders / db-morning-challenge Goto Github PK

I got this error message:
throw new Error('Environment variable DB_URL must be set');

The standard heroku url is called DATABASE_URL so might be worth changing in the code

• mentors table.
• posts table.
• likes table.

There is a step in the set up instructions that can be confusing.

This gives the reader 2 options and they might assume that the 1st one is the advised one.

If you do this method, the following step heroku config -s | grep USERS_DBdoesn't work because you have to go into Heroku itself to get the name of the database. There might be a quicker way but it is not explained and I had no idea myself!

I suggest that the first option be removed to make set up clearer.

Expected result should include:
book_id: 6, book_name: "Harry Potter...", max_reservation_time: 14

As the AVG time is 13.3.

@ameliejyc

People on my cohort mentioned that this workshop would have been clearer if they had seen the tables that each of the challenges was supposed to return.

We should add the expected results to the readme so that students now what they're looking for

I think the git clone link is for SSH connections when you have keys linked to Github. Maybe we should just take it out and tell them to git clone themselves and they can choose to use SSH or HTTPS?

line 120 in the readme

set up database and connect it online on elephant sql.

Create a books table and add a list of books.

Create a new database on Heroku: heroku addons:create heroku-postgresql:hobby-dev (You can also create the database with as alias heroku addons:create heroku-postgresql:hobby-dev --as USERS_DB)

• should be 'with AN alias'

As far as I can tell the README never asks students to do anything other than write SQL in their terminal. Since there's no node server involved could we remove the database/ folder and the dependencies on env2, pg and url? It'd be less stuff to keep up-to-date in future.

Just wondering what the purpose is of getting students to setup a new hosted DB instead of just a local one?

author = @bradreeder
Option 1 (maintenance by someone other than the author)

Author

--insert name--
--insert github handle--
--insert photo of author--

Maintainer

--insert name--
--insert github handle--
--insert photo of maintainer--

I was going to use the photos that FAC has of us from day 1 of the course. But obviously people would be free to update it with any photo of themselves that they wanted

My cohort (FAC10) ran into a number of issues using ElephantSQL (namely that it runs an older version of psql which doesn't include 'on confilct'.
I suggest switching from ElephantSQL to Heroku – many people on my course still felt uncomfortable hosting databases on heroku so I think it would be good to address this and teach it on the course.

Might be worth mentioning that when you run heroku config -s heroku gives you DATABASE_URL='[whateveritis]'. They need to remove the quotations in the config.env

Firstly, really cool. Nice work and I'm very glad you made it about books 💛

Some things we could improve:

1. There are some typos. If you don't mind I'll make a pull request for this ;)

2. Harry Potter was published in 1997. I take this seriously.

3. So far I've only done the warm-up exercise and it was quite hard for a warm-up. I'm not against it being hard, but maybe we can think about giving some more hints?

Going to do the rest in a bit...

But in the meantime, you are better than me at SQL. I'm pretty sure this works as it should, but can you tell me if it's the "right" way?

SELECT 
  book_id,
  book_name,
  max_reservation_time,
  library
FROM "public"."books" b1
WHERE max_reservation_time > (
  SELECT
    AVG(max_reservation_time)
  FROM books b2
  WHERE b2.library <= b1.library
  GROUP BY b1.library
);

In user testing, this challenge caused some confusion. We suggest changing the wording from:
AND to return only the books that can be reserved for a time greater than the average reservation time for all books at the library this book is in.

To:
AND to return only the books that can be reserved for a time greater than the average reservation time of its own library group. (hint: We're not trying to find the overall average across all libraries)

I think it's fairly likely that not all the students will have the heroku CLI as it seems like more and more people are using the heroku interface online to connect to github repos and auto-deploy etc.

As such this MC needs either a requirement for the heroku CLI at the top and mentors need to make sure everyone has this installed beforehand (as an hour is already short for this challenge and probably shouldn't waste time installing stuff) or(and?) we add instructions for adding a DB through the heroku website.

To make it clearer, we should maybe change the 'num' column in posts to 'id'. (We should also change this in likes to post_id)

In user testing the use of num caused a bit of confusion.

Should be in folder/file on master

in the seconds exercise you have to link the tables together like this:

SELECT mentors.name, count (post_num) FROM mentors
INNER JOIN posts
ON mentors.name = posts.mentor_name
INNER JOIN likes
ON posts.num = likes.post_num
GROUP BY mentors.name;

We are joining on names, and then on post_num.
Yesterday in our research we were led to believe that we should INNER JOIN on the IDs, not the names as those things can be variable, and joining on ID is then a consistent way for our databases to work.

I just wanted to raise an issue because I found it a bit confusing to join in a way I had been led to believe was bad practice.
(I may even have a wrong solution! Sorry if I'm wrong, just confused).

Could (should?) close the pool using connection.end() to stop this