current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

I am trying to export information about users of my Rails app (that's hosted by Heroku). I followed this tutorial to export a CSV file:

https://coderwall.com/p/jwtxjg

This involved the following:

  1. Connecting to post-gres database:

    psql "dbname=foo host=bar.compute-1.amazonaws.com user=abc password=123 port=5432 sslmode=require"

  2. Running a query:

    \copy (SELECT id, sign_in_count, last_sign_in_at, created_at FROM users) TO dump.csv CSV DELIMITER ','

This exported the CSV file to my current directory.

However, I'd also like to include an associated model in the query - for example, in my app, users created projects, and I'd like to export the number of projects each user has created.

How can I update my query to do this?

share|improve this question
    
Are the user created projects store in your database? – Oscar Valdez Esquea Sep 29 '14 at 21:40
    
yes, projects are stored on the same database – scientiffic Sep 29 '14 at 21:47
up vote 0 down vote

You'd try adding a count of the registries that matches the given user_id (Assuming there's a field called user_id). Something like this:

\copy (SELECT u.id, u.sign_in_count, u.last_sign_in_at, u.created_at, count(p.project) FROM users u, projects p WHERE p.user_id = u.id GROUP BY u.id, u.sign_in_count, u.last_sign_in_at u.created_at) TO dump.csv CSV DELIMITER ','

Assuming there'd only be 1 registry per user at the Users table, this should work.

share|improve this answer
    
thanks for your help. a project can have many users, so I don't have a single column (user_id) to reference the users of a particular project. I tried to update the query as follows: \copy (SELECT u.id, u.sign_in_count, u.last_sign_in_at, u.created_at, count(p.project) FROM users u, projects p WHERE p.users.pluck(:id).include? u.id GROUP BY u.id, u.sign_in_count, u.last_sign_in_at, u.created_at) TO dump.csv CSV DELIMITER ',' But I get the error: ERROR: syntax error at or near ":" LINE 1: ... ) FROM users u, projects p WHERE p.users.pluck ( :id ) .inc... The error refers to :id – scientiffic Sep 30 '14 at 14:23
    
How you make the relation between User and Project? Which column is it based on? – Oscar Valdez Esquea Sep 30 '14 at 14:31
    
a user has and belongs to many projects, and a project has and belongs to many users – scientiffic Sep 30 '14 at 14:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.