#Отрабатывает правильно
SELECT posts.id,
JSON_ARRAYAGG(users.avatar) AS avatars,
JSON_ARRAYAGG(users.lastname) AS lastnames
FROM posts
INNER JOIN likes ON likes.post_id = posts.id
INNER JOIN users ON users.id = likes.user_id
GROUP BY posts.id
SELECT posts.id,
posts.title,
posts.description,
posts.created_at,
JSON_ARRAYAGG(images.img) AS post_images,
users.id,
users.avatar,
users.firstname,
users.lastname,
(
SELECT COUNT(likes.id)
FROM likes
WHERE likes.post_id = posts.id
) as count_likes,
(
SELECT COUNT(comments.id)
FROM comments
WHERE comments.post_id = posts.id
) as count_comments,
(
SELECT COUNT(shares.id)
FROM shares
WHERE shares.post_id = posts.id
) as count_shares,
(
SELECT COUNT(images.id)
FROM images
WHERE images.post_id = posts.id
) as count_image,
(
SELECT
JSON_ARRAYAGG(users.lastname)
FROM posts
INNER JOIN likes ON likes.post_id = posts.id
INNER JOIN users ON users.id = likes.user_id
GROUP BY posts.id LIMIT 1
) AS like_lastnames,
(
SELECT
JSON_ARRAYAGG(users.avatar)
FROM posts
INNER JOIN likes ON likes.post_id = posts.id
INNER JOIN users ON users.id = likes.user_id
GROUP BY posts.id LIMIT 1
) AS like_avatars
FROM posts
INNER JOIN users ON users.id = posts.user_id AND users.id = 1
INNER JOIN images ON images.post_id = posts.id AND images.user_id = users.id
GROUP BY posts.id
Array
(
[0] => Array
(
[id] => 1
[title] => My Perfect Vacations in South America and Europe
[description] => Lorem ipsum dolor sit amet, consectadipisicing elit, sed do eiusmod por incidid ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud lorem exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis en aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt
[created_at] => 2021-08-18 10:42:17
[post_images] => ["post-photo1.jpg", "post-photo1.jpg"]
[avatar] => author-page.jpg
[firstname] => James
[lastname] => Spiegel
[count_likes] => 6
[count_comments] => 0
[count_shares] => 0
[count_image] => 2
[lastnames] => ["Spiegel", "Lname2", "Lname3", "Lname4", "Lname5", "Lname6"]
[avatars] => ["author-page.jpg", "friend-harmonic5.jpg", "friend-harmonic4.jpg", "friend-harmonic3.jpg", "friend-harmonic2.jpg", "friend-harmonic1.jpg"]
)
[1] => Array
(
[id] => 1
[title] => Advices for Backpacking
[description] => Lorem ipsum dolor sit amet, consectadipisicing elit, sed do eiusmod por incidid ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud lorem exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.
Duis en aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciun Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium, totam rem
[created_at] => 2021-08-18 10:42:17
[post_images] => ["post__thumb4.jpg"]
[avatar] => author-page.jpg
[firstname] => James
[lastname] => Spiegel
[count_likes] => 2
[count_comments] => 0
[count_shares] => 0
[count_image] => 1
[lastnames] => ["Spiegel", "Lname2", "Lname3", "Lname4", "Lname5", "Lname6"]
[avatars] => ["author-page.jpg", "friend-harmonic5.jpg", "friend-harmonic4.jpg", "friend-harmonic3.jpg", "friend-harmonic2.jpg", "friend-harmonic1.jpg"]
)
)
SELECT
message.id,
message.sender,
message.receiver,
users.username,
message.text_message,
message.created_at
FROM
message
INNER JOIN
users ON message.sender = users.id
WHERE
message.sender = 1
AND message.receiver = 2
OR message.sender = 2
AND message.receiver = 1
ORDER BY message.created_at;
SELECT
message.id,
message.sender,
message.receiver,
users.username,
message.text_message,
message.created_at
FROM
message
INNER JOIN
(SELECT
message.sender,
message.receiver,
MAX(message.created_at) AS max_created_at
FROM
message
WHERE
message.sender = 1
OR message.receiver = 1
GROUP BY message.sender , message.receiver) AS last ON message.created_at = last.max_created_at
AND message.sender = last.sender
AND message.receiver = last.receiver
INNER JOIN
users ON message.sender = users.id;
const express = require("express");
const run = require("./config/database");
const dotenv = require("dotenv");
dotenv.config({ path: "./config/config.env" });
const app = express();
app.use(express.json());
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "http://localhost:3000"); // update to match the domain you will make the request from
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});
const posts = require("./routes/posts");
app.use("/api/posts", posts);
app.get('/', (req, res) => {
res.send('Hello World!')
})
run();
const PORT = process.env.PORT || 5000;
const server = app.listen(
PORT,
console.log(`Example app listening at http://localhost:${PORT}`)
);
process.on("unhandledrejection", (err, promise) => {
console.log(`Error ${err.message}`);
server.close(() => process.exit(1));
});
const Posts = require("../models/Posts");
exports.index = async (req, res, next) => {
try {
const posts = await Posts.find();
res.status(200).json({ success: true, count: posts.length, data: posts });
} catch (err) {
res.status(400).json({ success: false });
}
};
exports.show = async (req, res, next) => {
try {
const posts = await Posts.findById(req.params.id);
if (!posts) {
res.status(400).json({ success: false });
}
res.status(200).json({ success: true, data: posts });
} catch (err) {
res.status(400).json({ success: false });
}
};
exports.create = async (req, res, next) => {
try {
const posts = await Posts.create(req.body);
res.status(200).json({ success: true, data: posts });
} catch (err) {
res.status(400).json({ success: false });
}
};
exports.update = async (req, res, next) => {
try {
const posts = await Posts.findByIdAndUpdate(req.params.id, req.body, {
new: true,
runValidators: true,
});
if (!posts) {
res.status(400).json({ success: false });
}
res.status(200).json({ success: true, data: posts });
} catch (err) {
res.status(400).json({ success: false });
}
};
exports.destroy = async (req, res, next) => {
try {
const posts = await Posts.findByIdAndDelete(req.params.id);
if (!posts) {
res.status(400).json({ success: false });
}
res.status(200).json({ success: true, data: {} });
} catch (err) {
res.status(400).json({ success: false });
}
};