How can I access the data of a property that's in another table? (postgres, express)
I'm creating a blog app. A user signs in and they can create a new post. To do this the data is being sent to the controller like so:
//blog-controller.js
blogController.create = (req, res) => {
Blog.create({
title: req.body.title,
content: req.body.content,
author_id: req.body.author_id,
// user_id: req.body.user.id //added user_id object
})
.then(blog => {
res.redirect(`/blog/${blog.id}`)
}).catch(err => {
console.log(err)
res.status(400).json(err);
});
};
I would like to assign the author of the blogpost to be the user that is currently signed in, the info that is created from my users controller:
//users-controller.js
usersController.create = (req, res) => {
User.create({
username: req.body.username,
email: req.body.email,
password_digest: hash,
})
How can I set the info of the user.username
to the blog.author_id
? (i.e. from two different tables)
Thanks in advance!
See also questions close to this topic
-
Legacy Query Sybase to TSQL MSSQL migration with multiple left join with or operator
I would like to convert this sybase legacy sql code to ansi tsql new standard for ms sql, but I can't find the right way. Any help would be appreciated.
SELECT 3 FROM x_linea_cred, x_linea_cred_priv, x_clt_prd WHERE x_clt_prd.r_client = @rclient AND (x_clt_prd.nro_prod *= x_linea_cred.nro_prod or x_clt_prd.nro_prod *= x_linea_cred_priv.nro_prod))
-
Implement the stored proc iterative approach in a sql manner
I am trying to implement convolution between two vectors v1 and v2. As part of that I have written a stored proc with an iterative approach using while loops. Here is the below code as below. I am not able to think through how to do it using sql as the stored proc is inefficient w.r.t performance. Can somebody share your thoughts on this. Any inputs would be appreciated.
Idea behing writing the stored proc:
https://software.intel.com/en-us/ipp-dev-reference-convolve
Table Schema and Sample Input Data:
CREATE TABLE AIRWork..TableA( idx INT, val INT ); CREATE TABLE AIRWork..TableB( idx INT, val INT ); INSERT INTO AIRWork..TableA VALUES( 0,-2 ); INSERT INTO AIRWork..TableA VALUES( 1, 0 ); INSERT INTO AIRWork..TableA VALUES( 2, 1 ); INSERT INTO AIRWork..TableA VALUES( 3,-1 ); INSERT INTO AIRWork..TableA VALUES( 4, 3 ); INSERT INTO AIRWork..TableB VALUES( 0, 0 ); INSERT INTO AIRWork..TableB VALUES( 1, 1 );
Stored Proc :
ALTER PROCEDURE Calc_Convolved_Values_Test AS BEGIN DECLARE @srclen1 INT DECLARE @srclen2 INT DECLARE @n INT = 0 DECLARE @k INT DECLARE @m INT DECLARE @SQL NVARCHAR(1000) DECLARE @x int DECLARE @xx int = 0 DECLARE @sum INT = 0 DECLARE @y int DECLARE @yy int = 0 DECLARE @a INT = 0 DECLARE @b INT = 0 SELECT @srclen1=COUNT(*) FROM AIRWork..TableA; SELECT @srclen2=COUNT(*) FROM AIRWork..TableB; SET @m = @srclen1 + @srclen2 -1 WHILE @n < @m BEGIN SET @k = 0 SET @sum = 0 WHILE @k <= @n BEGIN SET @SQL = 'SELECT @x=val FROM AIRWork..TableA WHERE idx ='+CONVERT(VARCHAR(5),@k) EXEC sp_executesql @SQL, N'@x int out', @xx out SET @a = @xx IF @n-@k < @srclen2 BEGIN SET @SQL = 'SELECT @y=val FROM AIRWork..TableB WHERE idx ='+CONVERT(VARCHAR(5),@n-@k) EXEC sp_executesql @SQL, N'@y int out', @yy out SET @b = @yy END ELSE BEGIN SET @b = 0 END SET @sum = @sum + @a*@b SET @k = @k + 1 END PRINT @sum SET @n = @n + 1 END END GO
Sample Output :
pDst[n] --> Please check the formula in the beginning of the question.
0 -2 0 1 -1 3
-
SQL - most performant solution for landing page-led return of results
I am looking for the fastest method to return results from 1 table, based on criteria listed in another table.
We have a number of landing pages on our website. Each has a record in our Landing table in an SQL 2016 database, thus:
ID int (primary key + identity)
Name varchar(30)
Criteria_Type varchar(30)
Criteria_Colour varchar(10)
Each landing page record describes criteria of the products to be displayed. Additionally, the user on the website can override some criteria such as colour. Also, the criteria in Landing may be null because we might want all Widget product types irrespective of colour.
At present, the fastest way I can return results is by a stored procedure, thus:
CREATE PROCEDURE dbo.GET_LANDING_RESULTS @LPID int, @PageNumber int, @Override_Colour varchar(10) AS BEGIN SET NOCOUNT ON DECLARE @Criteria_Type varchar(30) DECLARE @Criteria_Colour varchar(10) /* read criteria from landing table */ SELECT @Criteria_Type=Criteria_Type, @Criteria_Colour=Criteria_Colour FROM dbo.Landing WHERE ID = @ID IF @Override_Colour is not null @Criteria_Colour = @Override_Colour /* get results */ SELECT Count(ID) OVER() As TotalProducts, ItemID, Name, ProductType, Colour Price FROM dbo.Products WHERE (ProductType = @Criteria_Type OR @Criteria_Type is null) AND (Colour = @Criteria_Colour OR @Criteria_Colour is null) OFFSET @PageNumber ROWS FETCH NEXT 10 ROWS ONLY END
The web page passes through the ID number, page number and any colour override information and the procedure returns the products.
Is there a better way, perhaps with joins? There are many different criteria that can be applied - I have cut down this example.
-
How are you supposed to create Winston logger stream for Morgan in TypeScript
What is the correct way to create a winston logger in TypeScript that will log the express Morgan middleware logging? I found a number of JavaScript samples but have had trouble converting them over to TypeScript, because I get an error
Type '{ write: (message: string, encoding: any) => {}; logger: any; }' is not assignable to type '(options?: any) => ReadableStream'. Object literal may only specify known properties, and 'write' does not exist in type '(options?: any) => ReadableStream'.
Here is my code:
import { Logger, transports } from 'winston'; // http://tostring.it/2014/06/23/advanced-logging-with-nodejs/ // https://www.loggly.com/ultimate-guide/node-logging-basics/ const logger = new Logger({ transports: [ new (transports.Console)({ level: process.env.NODE_ENV === 'production' ? 'error' : 'debug', handleExceptions: true, json: false, colorize: true }), new (transports.File)({ filename: 'debug.log', level: 'info', handleExceptions: true, json: true, colorize: false }) ], exitOnError: false, }); if (process.env.NODE_ENV !== 'production') { logger.debug('Logging initialized at debug level'); } // [ts] // Type '{ write: (message: string, encoding: any) => {}; logger: any; }' is not assignable to type '(options?: any) => ReadableStream'. // Object literal may only specify known properties, and 'write' does not exist in type '(options?: any) => ReadableStream'. logger.stream = { write: function (message: string, encoding: any) { logger.info(message); }; } export default logger;
I have been able to work around this by adjusting my code to use
const winston = require('winston');
but would like to know how you are supposed to do this maintaining types? -
Insert the input into mysql
Im a newbie in programming. Excuse my ignorance. My english is not too good. Hope you understand what i mean in my question.
How can i get the value of id 'NILAI' below and insert into mysql.
html += '<tr>'; html += '<td class="indi" align="center">'; html += row.nis; html += '</td>'; html += '<td class="indi">'; html += row.nama_siswa; html += '</td>'; html += '<td>'; html += '<input id="nilai" type="text" placeholder="nilai"/>'; html += '</td>'; html += '</tr>';
I want the input value insert into mysql where col 'nis' in mysql table same with row. nis
I've been trying this but It's wrong. Its just read the value of the first row in input nilai and make a new nis=0.
var data1 = document.getElementById("nilai") ; var i; for (i=0;i<data1.length;i++) { var sql='INSERT INTO nilaitugas(no_tugas, nilai) VALUES ("'+dataid+'","'data1.elements[i]. value+'")'; }
Note:dataid is a variabel from url that i need to input.
-
specify path to pdf in node.js project that can be reached when deployed
I am deploying a
node.js
website on Google cloud platform, and I would like my keep my resume there, which is currently in my local directory. My code is as follows:import { Router } from 'express'; import * as express from 'express'; import * as path from 'path' ; import * as pug from 'pug' ; import * as fs from 'fs'; const index: Router = Router(); index.get('/curriculum-vitae', (req, res, next) => { var pdf_path = "local/path/to/resume.pdf" fs.readFile( pdf_path, (err, data) =>{ res.contentType('application/pdf'); res.send(data) }) });
However, I would like to change
pdf_path
to relative path so that when I deploy the app it can be found on the remote server. It is not clear how to do so sincefs.readFile
seem to only take absolute paths. -
Rails pg database: schema_migrations empty
I am trying to deploy a new release of a project to my staging server, but the schema_migrations table in the database is inexplicably empty.
It is now trying to run all migrations while deploying, causing issues since the other tables exist and are intact.
Instead of dropping/recreating the database and losing all my data(though inconvenient, a valid option), is it possible to generate the schema_migrations table without dropping?
-
node-postgres pool management
I'm trying to connect Nodejs to PostgreSQL database, for that I'm using node-postgres.
var pool = new Pool({ user: username, password: password, host: server database: database, max: 25 }); module.exports = { execute_query: function (query2) { //usage of query pool.query('query2', function(err, result){ return (result); }); } };
Then in my application, the function execute_query is called in different places in the application. Locally it works but I wonder how the pool is managed, is this enough configuration to manage concurrent users if my application is used by different people ? Do I need to do anything else to ensure that I have clients in the pool ? Or should I use the old way of managing clients with a hard code ?
I read the documentation of node-postgres and it says that pool.query is the simplist way but it doesnt say how it manages the connections... Do you have any information ?
Thank you
-
Is there an equivalent PostgresSQL window function (or alternate procedure) for the aggregate function bool_or()?
Given the following data:
select a,b from newtable; a | b ---+--- a | f a | f a | f b | f b | f b | t (6 rows)
The statement
select a, bool_or(b) from newtable group by a; a | bool_or ---+--------- a | f b | t
will produce a single row per distinct value (as expected from an aggregate function).
I was looking for an equivalent window function but seems that there is no such function in PostgreSQL. Is there any way to get the same result? Just to be clear I was looking for this result:
a | bool_or ---+--------- a | f a | f a | f b | t b | t b | t
-
Catching all errors in async-await express route handlers
Suppose I have a route like this:
app.get('/broken', (req, res) => { throw new Error('Broken!'); });
This will never send a response to clients.
However, I can add a middleware for all errors:
const errorMiddleware = (error, req, res, next) => { if (error) { console.error(error); return res.status(500) .json({ message: 'Internal server error', }); } next(error); };
But this will not work for
async
routes, because they do notthrow
directly.For example, this will not work:
app.get('/broken', async (req, res) => { throw new Error('Broken!'); });
So I can create a wrapper like this:
const asyncRoute = f => (req, res, next) => { return Promise.resolve(f(req, res, next)).catch(next); }; app.get('/broken', asyncRoute(async (req, res) => { throw new Error('Broken!'); }));
But this is a real pain, because now I have to call this function for every route!
What is a better way of handling this?
- The answer to Is there a way to wrap an await/async try/catch block to every function? is just what I describe above
- The answer to how to use Promise with express in node.js? does not use
await
-
setting content type for static javascript files in express
I am using
express
to serve up a page with js files using es6 modules. About es6 modules - https://jakearchibald.com/2017/es-modules-in-browsers/my server.js file is -
const app = express(); app.use( express.static( __dirname + '/src' )); app.get('*', (req, res) => { res.sendFile(path.join(__dirname + '/index.html')); }); app.listen(8080, () => console.log('Listening on port 8080!'));
and my index.html is -
<html lang="en"> <body> <script type="module" src="./src/test.js"></script> </body> </html>
In the file
test.js
i am using es6 modules, thus thetype="module"
in the script tag.But
test.js
script is not loading when i serve this html in browser. It is giving the error -Failed to load module script: The server responded with a non-JavaScript MIME type of "text/html". Strict MIME type checking is enforced for module scripts per HTML spec.
I tried adding the mime type for js files in express using this in the
server.js
file -express.static.mime.define({'application/javascript': ['js']});
But still the same result. I guess i need to somehow send the mime/content-type from express for js files, but how? Please help.
-
Validate input in Nodejs/Express back-end and send result to React front-end
I want to check if a domain (eg.
google.com
) that a user enters in my React front-end is valid.
I'm sending the domain to my Nodejs/Express back-end and using the Node functiondns.lookup
to check if the domain is valid as follows:app.post('/new-cert', function (req, res) { var lookup = dns.lookup(req.body.deletedCert, function (err, addresses, family) { console.log(addresses); //eg.74.125.224.72 or undefined }); // Only run this bit if `addresses` above is NOT `undefined` fs.appendFile('certs-list', '\n' + req.body.domainInput, function (err) { res.send('POST request: '); exec('sh cert-check-script-insert.sh'); if (err) throw err; }); });
If
addresses
isundefined
then I want to tell my React front-end that the domain entered is invalid and then it can print a relevant message to the user.
Else I want to run the rest of the function fromfs.appendFile
onwards to go ahead and insert the domain.Sorry I'm new to React/Node and was unable to find a post which could help me, any help is appreciated!