How can I access the data of a property that's in another table? (postgres, express)

2018-01-11 21:02 tjrules imported from Stackoverflow

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

What do we mean by SQL NOW() and created_at in SQL
I have been told to test someone's code and when I pass a json, it throws the following error

Error: No Rows found select * from otp_log where verified_at is NULL and (NOW()-created_at)<? and email_otp = ? and mobile_otp = ? 300,5581

Error: No rows found

I get this error when i send a put request from postman
```
{

    "signup_request_id": "36", 
    "email_otp": "5881"
}
```
Now I checked that we have otp_log table in our sql and it contains email_otp and mobile_otp field already populated with data.

The route i am using is signup/verify which as the name suggests is used to verify a user by otp.

So can someone help me with explaining what does this exactly do. like why am I getting the error (in general)? and what does this mean (NOW()-created_at?
(SQL ORACLE) How to generate html output, given select statement?
I'm working on oracle and taking report from database Oracle.
```
SELECT OPERATION, OBJECT_TYPE, CPU_COST, IO_COST FROM SYS.V_$SQL_PLAN;
```
I want to take a html output in oracle. I have this table in output but I want to see html formate. Is there any way to see that?
customTime class to sql time at persist jpa?
In my entity class I have field like this.
```
MyDateTime dateTime;
```
when I save this using JPA, I would need to convert this to sql time. What is the most easiest way to achieve this?

I couldn't find any way using a @PrePersist action.

Webpack build error for bootstrap

When I proceeded to build, the following "Warning" occurs. How do I fix this?

And is there no command to build in real time, other than "$ npm run build"? ex) $ sass --watch

Or live preview

  $ npm run build

    > bootstrap-webpack@1.0.0 build /Users/suk/myweb
    > webpack

    Hash: 62314a5e1bd8f234994f
    Version: webpack 4.16.1
    Time: 7100ms
    Built at: 2018-07-18 19:27:20
        Asset     Size  Chunks                    Chunk Names
    bundle.js  328 KiB       0  [emitted]  [big]  main
    [0] ./src/app.js 45 bytes {0} [built]
    [4] (webpack)/buildin/global.js 489 bytes {0} [built]
    [5] ./src/scss/app.scss 1.35 KiB {0} [built]
    [6] ./node_modules/css-loader!./node_modules/postcss-loader/lib??ref--4-2!./node_modules/sass-loader/lib/loader.js!./src/scss/app.scss 165 KiB {0} [built]
        + 6 hidden modules

    WARNING in configuration
    The 'mode' option has not been set, webpack will fallback to 'production' for this value. Set 'mode' option to 'development' or 'production' to enable defaults for each environment.
    You can also set it to 'none' to disable any default behavior. Learn more: https://webpack.js.org/concepts/mode/

    WARNING in asset size limit: The following asset(s) exceed the recommended size limit (244 KiB).
    This can impact web performance.
    Assets: 
      bundle.js (328 KiB)

    WARNING in entrypoint size limit: The following entrypoint(s) combined asset size exceeds the recommended limit (244 KiB). This can impact web performance.
    Entrypoints:
      main (328 KiB)
          bundle.js


    WARNING in webpack performance recommendations: 
    You can limit the size of your bundles by using import() or require.ensure to lazy load some parts of your application.
    For more info visit https://webpack.js.org/guides/code-splitting/

Npm test fail on run

I have created a project with create-react-app with typescript scripts. The 'npm start' is working correctly, but when I try to run 'npm run test' it is failing like so (full log):

0 info it worked if it ends with ok
1 verbose cli [ 'c:\\tools\\nodejs\\6.9.1\\node.exe',
1 verbose cli   'c:\\tools\\nodejs\\6.9.1\\node_modules\\npm\\bin\\npm-cli.js',
1 verbose cli   'run',
1 verbose cli   'test' ]
2 info using npm@3.10.8
3 info using node@v6.9.1
4 verbose run-script [ 'pretest', 'test', 'posttest' ]
5 info lifecycle pspm-frontend-typescript@0.1.0~pretest: pspm-frontend-typescript@0.1.0
6 silly lifecycle pspm-frontend-typescript@0.1.0~pretest: no script for pretest, continuing
7 info lifecycle pspm-frontend-typescript@0.1.0~test: pspm-frontend-typescript@0.1.0
8 verbose lifecycle pspm-frontend-typescript@0.1.0~test: unsafe-perm in lifecycle true
9 verbose lifecycle pspm-frontend-typescript@0.1.0~test: PATH: c:\tools\nodejs\6.9.1\node_modules\npm\bin\node-gyp-bin;C:\Users\M013435\WebstormProjects\pspm-frontend-typescript\node_modules\.bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\oraclexe\app\oracle\product\11.2.0\server\bin;;C:\ProgramData\Oracle\Java\javapath;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;;c:/tools\devtool\1.37\bin;c:/tools\git\2.13.2\bin;c:/tools\winpython\2.7.12.4\scripts;c:/tools\curl\7.56.1\bin;c:/tools\maven\3.5.2\bin;c:/tools\jdk\1.8.152.64\bin;c:/tools\yarn\1.0.2\bin;c:/tools\gradle\4.3.1\bin;;c:/tools\nodejs\6.9.1\.;
10 verbose lifecycle pspm-frontend-typescript@0.1.0~test: CWD: C:\Users\M013435\WebstormProjects\pspm-frontend-typescript
11 silly lifecycle pspm-frontend-typescript@0.1.0~test: Args: [ '/d /s /c', 'react-scripts-ts test --env=jsdom' ]
12 silly lifecycle pspm-frontend-typescript@0.1.0~test: Returned: code: 1  signal: null
13 info lifecycle pspm-frontend-typescript@0.1.0~test: Failed to exec test script
14 verbose stack Error: pspm-frontend-typescript@0.1.0 test: `react-scripts-ts test --env=jsdom`
14 verbose stack Exit status 1
14 verbose stack     at EventEmitter.<anonymous> (c:\tools\nodejs\6.9.1\node_modules\npm\lib\utils\lifecycle.js:255:16)
14 verbose stack     at emitTwo (events.js:106:13)
14 verbose stack     at EventEmitter.emit (events.js:191:7)
14 verbose stack     at ChildProcess.<anonymous> (c:\tools\nodejs\6.9.1\node_modules\npm\lib\utils\spawn.js:40:14)
14 verbose stack     at emitTwo (events.js:106:13)
14 verbose stack     at ChildProcess.emit (events.js:191:7)
14 verbose stack     at maybeClose (internal/child_process.js:877:16)
14 verbose stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:226:5)
15 verbose pkgid pspm-frontend-typescript@0.1.0
16 verbose cwd C:\Users\M013435\WebstormProjects\pspm-frontend-typescript
17 error Windows_NT 6.1.7601
18 error argv "c:\\tools\\nodejs\\6.9.1\\node.exe" "c:\\tools\\nodejs\\6.9.1\\node_modules\\npm\\bin\\npm-cli.js" "run" "test"
19 error node v6.9.1
20 error npm  v3.10.8
21 error code ELIFECYCLE
22 error pspm-frontend-typescript@0.1.0 test: `react-scripts-ts test --env=jsdom`
22 error Exit status 1
23 error Failed at the pspm-frontend-typescript@0.1.0 test script 'react-scripts-ts test --env=jsdom'.
23 error Make sure you have the latest version of node.js and npm installed.
23 error If you do, this is most likely a problem with the pspm-frontend-typescript package,
23 error not with npm itself.
23 error Tell the author that this fails on your system:
23 error     react-scripts-ts test --env=jsdom
23 error You can get information on how to open an issue for this project with:
23 error     npm bugs pspm-frontend-typescript
23 error Or if that isn't available, you can get their info via:
23 error     npm owner ls pspm-frontend-typescript
23 error There is likely additional logging output above.
24 verbose exit [ 1, true ]

It is not very descriptive for me. My node version is 6.9.1 and npm is 3.10.8. I have updated node to version 9 and updated npm. I have also tried to clean npm cache and remove node_modules, but the error remains.

bot framework default dialog is not triggering out
I am creating a multi-dialog chatbot with Microsoft bot framework using node.js bot is connecting with LUIS for NLP. when the intent is matching dialog is triggering without any issues. but when it comes to a none matching dialog I want to trigger this Sorry, I don't know. :( this is the default dialog message. my code is as below and it is not working as expected for none matching intents.

I noted that every time I typed a word phrase which is not matched to any of the intents it triggers the previously matched intent dialog.

I don't know what's wrong with this code. I followed the below solutions but none work for me

solution 01

solution 02

solution 03
```
const bot = new builder.UniversalBot(new builder.ChatConnector({
        appId: process.env.MicrosoftAppId,
        appPassword: process.env.MicrosoftAppPassword
    }));

 bot.dialog(vacanciesDialog1.id, vacanciesDialog1.waterfall).triggerAction({ matches: vacanciesDialog1.name });
    bot.dialog(gratuityDialog.id, gratuityDialog.waterfall).triggerAction({ matches: gratuityDialog.name });
    bot.dialog(slipsDialog.id, slipsDialog.waterfall).triggerAction({ matches: slipsDialog.name });
```
default dialog as following if any dialog is not matched
```
bot.dialog('/', [
        (session, args, next) => {
            session.send(`Sorry, I don't know. :( this is the default dialog`);
        }
    ])
```

Error When migrating after using 2 foreign keys of same table

in my model I have to have 2 foreign keys that are of same model. i tried related name so makemigrations works fine. but when i'm trying to migrate, it throws an error and says

django.db.utils.ProgrammingError: column "service_requested_by_id" cannot be cast automatically to type uuid
HINT:  You might need to specify "USING service_requested_by_id::uuid".

my model

class Schedule(BaseModel):
    project = models.ForeignKey("projects.Project",null=True,blank=True)
    description = models.TextField(blank=True,null=True)
    vehicle = models.ForeignKey("logistics.Vehicle",null=True,blank=True)
    driver = models.ForeignKey("staffs.Staff",related_name='driver',blank=True,null=True)
    service_requested_by = models.ForeignKey("staffs.Staff",related_name='service_requested_by',blank=True,null=True)
    start_time = models.DateTimeField(null=True,blank=True)
    end_time = models.DateTimeField(null=True,blank=True)
    is_deleted = models.BooleanField(default=False)
    starting_km = models.DecimalField(null=True,blank=True,default=0,decimal_places=0, max_digits=15,validators=[MinValueValidator(Decimal('0'))])
    completed_km = models.DecimalField(null=True,blank=True,default=0,decimal_places=0, max_digits=15,validators=[MinValueValidator(Decimal('0'))])
    travelled_km = models.DecimalField(null=True,blank=True,default=0,decimal_places=0, max_digits=15)

    class Meta:
        db_table = 'schedule'
        verbose_name = _('schedule')
        verbose_name_plural = _('schedules')
        ordering = ('vehicle',)  

    class Admin:
        list_display = ('vehicle',)

    def __unicode__(self):
        if self.vehicle:
            return self.vehicle.name
        else:
           return str(self.project)

It was working all fine till i added the service_requested_by field. any hint what i am possibly doing wrong? do i need to post anything else beside this code?

Postgres Sync Remote server with local server and delete synced rows in local server

I have a setup where I have multiple(100s) clients writing data to a local server(on local LAN). Master server resides on AWS cloud connected to this local server via internet. Schema design is exactly same on both servers. I need to be able to sync all records written to this local server to master periodically. Records which are synced also needs to be deleted from local server.

Are there any off the shelf solutions available for this(free or paid)? I had a look at postgres docs on replicas but none of the method seem to fit in my requirement.

My Postgres version is 9.6

This question has been marked duplicate but please note that only data write operations need to be synchronised and not delete operations. I am not looking for exact replica or multi-master solution here. Local server is only for write purposes, no read operations will be performed on it except for syncing purposes.

Postgres Trigrams and ordering verly slow

I am trying to build up a fuzzy search against multiple columns with weightings for each column's distance from the corresponding search term.

I have the following query -

select sf_id 
from (
  select * 
  from (
     select sf_id , 
            (1.0 - cast(coalesce(mailingcity, '') <->> 'san ant' as float)) * 3.0  as score  
     from contacts 
     order by score desc 
     limit 1000
  ) as mailingcity

  union

  select * 
  from (
    select sf_id, 
           (1.0 - cast(coalesce(lastname, '') <->> 'anders' as float)) * 5.0 as score  
    from contacts 
    order by score 
    desc limit 1000
  ) as lastname
) 
as agg 
group by sf_id 
order by sum(score) desc

with indexes such as the following -

create index contact_lastname_trgrm_idx on contacts using gin (lastname gin_trgm_ops)

on the columns used to match on.

We have ~ 500,000 records in the table and the query is taking over three seconds.

I also have expression indexes on the coalesce function

Is there any way to speed this up?

Result of EXPLAIN -

Sort  (cost=212791.05..212791.55 rows=200 width=154) (actual time=3165.154..3165.247 rows=2000 loops=1)
  Sort Key: (sum(((('1'::double precision - (((COALESCE(contacts.mailingcity, ''::character varying))::text <->> 'san ant'::text))::double precision) * '3'::double precision)))) DESC
  Sort Method: quicksort  Memory: 205kB
  ->  GroupAggregate  (cost=212766.41..212783.41 rows=200 width=154) (actual time=3163.855..3164.621 rows=2000 loops=1)
        Group Key: contacts.sf_id
        ->  Sort  (cost=212766.41..212771.41 rows=2000 width=154) (actual time=3163.847..3163.966 rows=2000 loops=1)
              Sort Key: contacts.sf_id
              Sort Method: quicksort  Memory: 205kB
              ->  HashAggregate  (cost=212616.75..212636.75 rows=2000 width=154) (actual time=3155.719..3156.055 rows=2000 loops=1)
                    Group Key: contacts.sf_id, ((('1'::double precision - (((COALESCE(contacts.mailingcity, ''::character varying))::text <->> 'san ant'::text))::double precision) * '3'::double precision))
                    ->  Append  (cost=106166.70..212606.75 rows=2000 width=154) (actual time=1629.241..3154.841 rows=2000 loops=1)
                          ->  Limit  (cost=106166.70..106283.37 rows=1000 width=27) (actual time=1629.241..1629.798 rows=1000 loops=1)
                                ->  Gather Merge  (cost=106166.70..154807.03 rows=416888 width=27) (actual time=1629.239..1629.730 rows=1000 loops=1)
                                      Workers Planned: 2
                                      Workers Launched: 2
                                      ->  Sort  (cost=105166.68..105687.79 rows=208444 width=27) (actual time=1589.059..1589.232 rows=1021 loops=3)
                                            Sort Key: ((('1'::double precision - (((COALESCE(contacts.mailingcity, ''::character varying))::text <->> 'san ant'::text))::double precision) * '3'::double precision)) DESC
                                            Sort Method: external merge  Disk: 7256kB
                                            ->  Parallel Seq Scan on contacts  (cost=0.00..81763.88 rows=208444 width=27) (actual time=0.145..1405.681 rows=166755 loops=3)
                          ->  Limit  (cost=106166.70..106283.37 rows=1000 width=27) (actual time=1524.305..1524.912 rows=1000 loops=1)
                                ->  Gather Merge  (cost=106166.70..154807.03 rows=416888 width=27) (actual time=1524.304..1524.842 rows=1000 loops=1)
                                      Workers Planned: 2
                                      Workers Launched: 2
                                      ->  Sort  (cost=105166.68..105687.79 rows=208444 width=27) (actual time=1455.159..1455.386 rows=1016 loops=3)
                                            Sort Key: ((('1'::double precision - (((COALESCE(contacts_1.lastname, ''::character varying))::text <->> 'anders'::text))::double precision) * '5'::double precision)) DESC
                                            Sort Method: external merge  Disk: 7280kB
                                            ->  Parallel Seq Scan on contacts contacts_1  (cost=0.00..81763.88 rows=208444 width=27) (actual time=0.373..1290.368 rows=166755 loops=3)
Planning time: 0.855 ms
Execution time: 3218.589 ms

As per the advice from @a_horse_with_no_name below, the following query now runs at ~250ms

select sf_id from (select * from (
select sf_id , 
(1.0 - cast(coalesce(mailingcity, '') <->> 'san ant' as float)) * 3.0 
as score  
from contacts 
    where mailingcity % 'san ant' 
    order by score desc limit 1000) as mailingcity
union all
select * from (
select sf_id, 
(1.0 - cast(coalesce(lastname, '') <->> 'anders' as float)) * 5.0 as score  
from contacts 
    where lastname % 'anders'
    order by score desc limit 1000) as lastname) 
as agg group by sf_id order by sum(score) desc

Best way to add a React frontend to a existing Node.js / Express REST API backend
I have an existing backend with a REST API written using node.js/express. I can call urls e.g. /getallhouses and get the corresponding JSON object. Using a mobile app, this is rather straightforward as you just call the REST API and process the data. As I need to also add a webapp e.g. with React.js, it seems more murky.

What is the best way of implementing this webapp strategy?

Creating a stand-alone react.js app which will be called by the user first and then is using the REST API like a mobile app and hosting that react.js app on a different server? Or is it better to start from the already existing express/node backend and serving the initial index.html file by
```
res.sendFile(path.join(__dirname + 'views/index.html'));
```
and putting all react.js related code into views and letting the backend serving the required react.js app to the user?
How does node-express listen both on localhost and local IP?

I started using node express not too long and developed a simple http server using the default settings.

I have always tested it with requests to localhost:3000 with Postman and on the browser and everything was fine.

Then I needed to move the service on the local network so that even applications not running on my machine can access it.

I got my local IP and before perform any modification I tried first with Postman using the IP address instead of localhost and also with the browser on my smartphone to check what would have happened. With my surprise it was already working on the local network, in the sense that I got a response message from the server (which might be dangerous since I did not even know).

I guess the line where you specify which address and port the server should listen on is server.listen(port);

If the address is not specified does it mean that it should listen both to localhost and local IP? Or is node-express automatically forwarding from one address to the other? Otherwise how can it work both on localhost and local IP?
I want to implement peer to peer video call in nodejs using webrtc

i want to call a route and then that route will open in browse and starts transmitting video stream . i used getUserMedia api .

Related questions

How can I access the data of a property that's in another table? (postgres, express)

See also questions close to this topic