George Opritescu ♥ Developer from somewhere

ERROR invalid reference to FROM-clause

I was receiving this error:

ERROR: invalid reference to FROM-clause entry for table “mailboxer_conversations” LINE 3: … ON “mailboxer_notifications”.”conversation_id” = “mailboxer… ^ HINT: There is an entry for table “mailboxer_conversations”, but it cannot be referenced from this part of the query.

Because I had a query like this:

SELECT  DISTINCT "mailboxer_conversations".* FROM "mailboxer_conversations",
(select count(*) from mailboxer_receipts where mailboxer_receipts.is_read = 't') as read_count
INNER JOIN "mailboxer_notifications" ON "mailboxer_notifications"."conversation_id" = "mailboxer_conversations"."id"
INNER JOIN "mailboxer_receipts" ON "mailboxer_receipts"."notification_id" = "mailboxer_notifications"."id"
WHERE "mailboxer_receipts"."receiver_id" = 118
ORDER BY "mailboxer_conversations"."id" ASC LIMIT 1000

Fixed it by rewriting the query like this:

SELECT  DISTINCT "mailboxer_conversations".*,
(select count(*) from mailboxer_receipts where mailboxer_receipts.is_read = 't') as read_count
FROM "mailboxer_conversations"
INNER JOIN "mailboxer_notifications" ON "mailboxer_notifications"."conversation_id" = "mailboxer_conversations"."id"
INNER JOIN "mailboxer_receipts" ON "mailboxer_receipts"."notification_id" = "mailboxer_notifications"."id"
WHERE "mailboxer_receipts"."receiver_id" = 118
ORDER BY "mailboxer_conversations"."id" ASC LIMIT 1000

postgresql

git diff when you have a file with the same name as a branch

Let’s say you have a file called potato, and a branch called potato. If you want to see a diff against the branch called potato you’d run this:

git diff potato --

If you want to see a diff of the file potato:

git diff -- potato

git

pg_dump

Example of using pg_dump:

pg_dump -F c -U your_username -h some_host.rds.amazonaws.com -d your_db_name

pg_dump postgresql

sed regex replace backreference example

I had a file containing something like this:

ENV['SOME_VAR'] = "some_value"
ENV['SOME_OTHER_VAR'] = "some_other_value"

and I wanted to turn it into a shell script that would export those vars. This comes pretty close to it, and shows an example of using replace using sed backreferences:

cat unicorn.conf  | grep ENV | sed "s/ENV\['\(.*\)'.*\]/export \1/g" | sed 's/ = /=/g'

sed regex backreference

scp example

Here’s how to scp a file, using a pem file:

scp -i ~/my_key.pem ubuntu@some.host:/tmp/some_file.dump .

scp

launch an editor to write/edit a shell command

If you have a long command that you want to modify, hold Ctrl-X and press e. This will open the editor specified with $EDITOR, and you can write it there.

shell

finding git branches that contain a commit

Information found here:

git branch --contains SOMESHA

and to find only local ones:

git branch -r --contains SOMESHA

git

creating and debugging mongo indexes

To check index usage, start your mongod with -vv flags, example:

/usr/bin/mongod -vv --config /etc/mongod.conf

And then cat your logfile, probably in this location:

cat /var/log/mongodb/mongod.log

Look for the nscanned and nscannedObjects fields.

mongo index

iframe, cookies and IE

If you need to accept cookies from inside an iframe with IE, you need to set the P3P header: P3P: CP=”whatever P3P keywords”

or

P3P: CP=”we do not support P3P”

And this will make IE happy.

iframe cookie internet explorer

find out your RAM type under ubuntu

sudo lshw -short -C memory

linux ubuntu ram