0

(Please correct my if i do some mistakes)

I have 2 tables:

  • ACTIVITIES : ID / NAME / CONTENT / DATE /ETC..
  • ARTICLES : ID / NAME /CONTENT /DATE /ETC..

I have created one script to delete image when it is not in the db , the problem is : I don't know how to check the content of activity and article on the same request because this request bellow just delete my activities images..

#!/bin/bash

db="intranet_carc_development"
user="benjamin"

echo "DELETING UNUSED FILES AND IMAGES..."
for f in public/uploads/files/*
do
  if [[ -f "$f" ]]
  then
    f="$(basename "$f")"
    psql $db $user -t -v "ON_ERROR_STOP=1" \
    -c "select content from public.articles where content like '%$f%'" | grep . \
    && echo "exist" \
    || rm public/uploads/files/$f
  fi
done
printf "DONE\n\n"

If tied something like : 

select content from public.articles, public.activities where content like '%$f%'"

but I have this log error: 

ERROR:  column reference "content" is ambiguous
Benjamin Barbé
  • 281
  • 2
  • 4
  • 16

1 Answers1

1

You can try something like 

WITH artcontent AS (
    SELECT content
    FROM public.articles
),
actcontent AS (
    SELECT content
    FROM public.activities
),
merge AS (
    SELECT * FROM artcontent 
    UNION ALL
    SELECT * FROM actcontent 
)
SELECT *
FROM merge

The UNION ALL statement will put together your two results artcontent (which comes from articles) and actcontent (from activities).

Hope it will help you !

CsuGouv
  • 249
  • 1
  • 9