#Отрабатывает правильно
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.idArray
(
[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 });
}
};SELECT motherboards.id,
chipsets.chipset_name,
brends.brend_name,
series.series_name,
connectors.connector_name,
motherboards.motherboard_name FROM asus.parametrs
JOIN asus.chipsets ON parametrs.chipset_id = chipsets.id
JOIN asus.brends ON parametrs.brend_id = brends.id
JOIN asus.series ON parametrs.series_id = series.id
JOIN asus.connectors ON parametrs.connector_id = connectors.id
JOIN asus.motherboards ON parametrs.motherboard_id = motherboards.id AND
(
chipsets.id = 2 AND
brends.id = 1 AND
series.id = 1 AND
connectors.id = 1 AND
motherboards.id = 4
)
ORDER BY parametrs.motherboard_id;DROP DATABASE `asus`;
CREATE DATABASE `asus`;
USE `asus`;
CREATE TABLE asus.chipsets
(
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`chipset_name` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE asus.brends
(
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`brend_name` VARCHAR(30) NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE asus.series
(
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`series_name` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE asus.connectors
(
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
`connector_name` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE asus.motherboards
(
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
`motherboard_name` VARCHAR(255) NOT NULL,
`motherboard_image` VARCHAR(255) NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE asus.parametrs
(
#id INT UNSIGNED AUTO_INCREMENT NOT NULL,
`chipset_id` INT UNSIGNED NOT NULL,
`brend_id` INT UNSIGNED NOT NULL,
`series_id` INT UNSIGNED NOT NULL,
`connector_id` INT UNSIGNED NOT NULL,
`motherboard_id` INT UNSIGNED NOT NULL,
FOREIGN KEY(`chipset_id`) REFERENCES chipsets(`id`),
FOREIGN KEY(`brend_id`) REFERENCES brends(`id`),
FOREIGN KEY(`series_id`) REFERENCES series(`id`),
FOREIGN KEY(`connector_id`) REFERENCES connectors(`id`),
FOREIGN KEY(`motherboard_id`) REFERENCES motherboards(`id`),
PRIMARY KEY(`chipset_id`, `brend_id`, `series_id`, `connector_id`, `motherboard_id`)
);
INSERT INTO asus.chipsets(`chipset_name`) VALUES
('Intel B560'),
('Intel Z590');
INSERT INTO asus.brends(`brend_name`) VALUES
('Intel'),
('AMD');
INSERT INTO asus.series(`series_name`) VALUES
('PRIME'),
('ProArt'),
('ROG - Republic of Gamers'),
('TUF Gaming');
INSERT INTO asus.connectors(`connector_name`) VALUES
('LGA 1200'),
('AM4');
INSERT INTO asus.motherboards
(
`motherboard_name`,
`motherboard_image`
)
VALUES
('PRIME Z590M-PLUS', 'https://dlcdnwebimgs.asus.com/gain/109a7d9b-f938-48b5-a8fa-ec1b73210558/w185'),
('PRIME Z590M-PLUS', 'https://dlcdnwebimgs.asus.com/gain/17aff855-1b28-4a7d-8ca1-9aaf8418b723/w185'),
('PRIME Z590-A', 'https://dlcdnwebimgs.asus.com/gain/276acda5-c2eb-4d3e-8b35-844eed31d025/w185'),
('PRIME B560-PLUS', 'https://dlcdnwebimgs.asus.com/gain/f8c4b5c6-002c-4b97-8cf5-13a2fb63ea41/w185');
INSERT INTO asus.parametrs
(
`chipset_id`,
`brend_id`,
`series_id`,
`connector_id`,
`motherboard_id`
)
VALUES
(2, 1, 1, 1, 1),
(2, 1, 1, 1, 2),
(2, 1, 1, 1, 3),
(1, 1, 1, 1, 4);
SELECT * FROM asus.parametrs;