I can not find the right sql query

Navigation
0

I have a question in which I need to know the number of updates since the last time a user enters a thread .

Each time a user enters a thread, the following query is executed: 

INSERT INTO visita_post ('postId', 'nick', 'fecha_ultimo_acceso') 
            VALUES(?, ?, sysdate()) 
            ON DUPLICATE KEY UPDATE fecha_ultimo_acceso = sysdate()'

And it is added to the table visita_post that has the following structure: 

postId                 int(11)
nick                   varchar(30)
fecha_ultimo_acceso    datatime

To see how many updates have been made since the last visit, I run the following query: 

SELECT  
        p.count(id) nActualizacion,p.hiloid,
        p.post_tema
        from post p, visita_post v 
        where p.post_tema = v.postId 
        and p.fecha > v.fecha_ultimo_acceso 
        and v.nick = ?
        and p.hiloid = ?
        group by p.hiloid,
        p.post_tema

The query shows me how many new posts there have been since the last time the insert was executed but this query has several problems that I can not solve.

  • If you have never entered a thread it does not show any updates but I need to show all the updates. This part is a bit confusing written in this way, so I will try to explain it with examples.

If you execute the second query without using the visita_post table with this query: 

SELECT  count(id) nActualizacion,
        post_tema,hiloid
        from post
        where 
        hiloid is not null
        GROUP By post_tema,hiloid

I get the result in nActualizacion the total number of updates that exist. I would like that in case the user has never executed the first query the data of this one will be shown.

  • If there are no updates, I need you to return 0, instead of no rows.

EDITION

Right now I have the following query: 

SELECT  COUNT(DISTINCT
                CASE
                    WHEN p.fecha > v.fecha_ultimo_acceso THEN v.postID
                    WHEN v.postId IS NULL THEN p.id
                END) nActualizacion,
        p.hiloid,
        p.post_tema,
        v.fecha_ultimo_acceso
FROM post p
LEFT JOIN ( SELECT *
            FROM visita_post
            WHERE nick = "testing") v
    ON p.post_tema = v.postId
    AND p.fecha > v.fecha_ultimo_acceso
WHERE p.hiloid = 27
and p.post_tema is not null

GROUP BY p.hiloid,
         p.post_tema

If I execute it like this right now it does not count the results well, but executing it in the same way adding a and p.fecha > v.fecha_ultimo_acceso in the line before group by shows me the result correctly but it does not show me the rows that do not exist in the table visita_post .

If you need more information about the database, ask for it in the comments and attach it

    
asked by Lombarda Arda 08.02.2018 в 15:45
source

2 answers

0

At the end the query that has given me results is the following: 

SELECT  
    COUNT(DISTINCT
                CASE
                    WHEN p.fecha > v.fecha_ultimo_acceso THEN v.postID
                    WHEN v.postId IS NULL THEN p.id
                END) nActualizacion,
                p.hiloid,
    p.post_tema
FROM post p LEFT JOIN visita_post v
ON p.post_tema = v.postId
and v.nick ="testing"
where p.post_tema is not null


GROUP BY p.hiloid, p.post_tema  
ORDER BY 'p'.'post_tema'  DESC
    
answered by 09.02.2018 / 08:13
source
0

The name of your fields is confusing. In the visita_post table you save a postId but it is not the id in the post table, but it refers to the field post_tema . On the other hand, you have a field hiloid that should identify the thread that contains the posts.

But good, assuming that the relationship is well done and effectively postId has equivalence in post_tema ... my answer is as follows:

If a user has never entered a thread (post_tema) and you want to show all the updates in that case, you should use: 

SELECT  
    count(p.id) nActualizacion,
    p.hiloid,
    p.post_tema
FROM post p LEFT JOIN visita_post v 
     ON p.post_tema = v.postId 
     AND v.nick = ?
WHERE p.hiloid = ?
  AND p.fecha >= IFNULL(v.fecha_ultimo_acceso, p.fecha)
GROUP BY p.hiloid, p.post_tema

That way, if there are no records in visita_post for that thread and that user, it is always true that p.fecha is > = a p.fecha and it will return all the posts in that thread.

EDIT had p.count(id) instead of count(p.id) . Thanks for the correction

    
answered by 08.02.2018 в 19:37
Problem with library MPAndroidChart when trying to use a fragment method from my main activity Vector as input of a fortran 90 function